The fastest way to count values in a range

[avi]

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

 

[Bob Phillips]

=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]

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

 

[Bob Phillips]

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)