Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The fastest way to count values in a range
Hello,
I'm writing an application that counts values in a large selection if they comply to a crieria of greater than Firstvalue and smaller than secondvalue . The selection could be a huge Excel range, so that the speed is a very important factor. I use a procedure like: For Each cell In Selection If cell.Value <= secondvalue And cell.Value = Firstvalue Then R = R + 1 Next cell Is it the fastest way or are there better ones (Excel functions?) All the data are numeric Thanks a lot Avi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The fastest way to count values in a range
=SUMPRODUCT(--(A1:A100<=secondvalue),--(A1:A100=firtsvalue))
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "avi" wrote in message ups.com... Hello, I'm writing an application that counts values in a large selection if they comply to a crieria of greater than Firstvalue and smaller than secondvalue . The selection could be a huge Excel range, so that the speed is a very important factor. I use a procedure like: For Each cell In Selection If cell.Value <= secondvalue And cell.Value = Firstvalue Then R = R + 1 Next cell Is it the fastest way or are there better ones (Excel functions?) All the data are numeric Thanks a lot Avi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The fastest way to count values in a range
Hi Bob,
Very fast and impressive, but frankly it's the first time i saw such a formula. Usually I use SUMPRODUCT for a cross multiplication. Could you expalin it a little bit so i can integrate it in my VBA code ? Thanks again Avi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
The fastest way to count values in a range
It's all explained at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
If you want to use it in VBA, use myVal = Activesheet.Evaluate("SUMPRODUCT(--(A1:A100<=secondvalue),--(A1:A100=firstvalue))") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "avi" wrote in message oups.com... Hi Bob, Very fast and impressive, but frankly it's the first time i saw such a formula. Usually I use SUMPRODUCT for a cross multiplication. Could you expalin it a little bit so i can integrate it in my VBA code ? Thanks again Avi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Max values in a range | Excel Worksheet Functions | |||
Fastest way to select large range (e.g. B3:F1002)? | Excel Discussion (Misc queries) | |||
What is the fastest way to copy a range to a 2D array? | Excel Programming | |||
fastest sorting routine for 2-D array of long values | Excel Programming | |||
Count the how many different values in a range | Excel Worksheet Functions |