counting functions

[Soz]

Hi,
I have a long column list of numbers that I need to count instances of.
example: the instances of numbers >=0 & <=3, >=4 & <=7, etc.
Thanks,
Soz

 

[Biff]

Hi!

Assume the list is in column A:

=SUMPRODUCT(--(A1:A20>=0),--(A1:A20<=3))

Or, put the number ranges in two cells:

B1 = 0
C1 = 3

=SUMPRODUCT(--(A1:A20>=B1),--(A1:A20<=C1))

Biff

 

[Soz]

Thanks Biff,
That works just the way I needed it to. The formula makes sense, but what is
the purpose of the two -- in the formula.
Thanks,
Soz

 

[Biff]

Hi!

See:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Biff

 

[RagDyeR]

And this saves 4 keystrokes and does *exactly* the same thing!

=SUMPRODUCT((A1:A20>=0)*(A1:A20<=3))

It seems to be the "fashion" to use the unary, even when unnecessary.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


Thanks Biff,
That works just the way I needed it to. The formula makes sense, but what is
the purpose of the two -- in the formula.
Thanks,
Soz