The fastest way to count values in a range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

avi - 24 Sep 2007 20:43 GMT

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

Reply to this Message

Bob Phillips - 24 Sep 2007 20:50 GMT

=SUMPRODUCT(--(A1:A100<=secondvalue),--(A1:A100>=firtsvalue))

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello,
>
[quoted text clipped - 18 lines]
> Thanks a lot
> Avi

Reply to this Message

avi - 24 Sep 2007 21:08 GMT

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 to this Message

Bob Phillips - 24 Sep 2007 22:46 GMT

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))")

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Bob,
>
[quoted text clipped - 6 lines]
> Thanks again
> Avi

Reply to this Message