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

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

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

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