Thread: Count in Range.

You could use SUMPRODUCT

Code:

=SUMPRODUCT(--(range>=3.5),--(range<=5.6))

(substitute "range" with your actual range address)

Or you could use 2 COUNTIFs

Code:

=COUNTIF(range,">=3.5")-COUNTIF(range,">5.6")

or... written another way

Code:

=SUM(COUNTIF(range,{">=3.5",">5.6"})*{1,-1})

Of course if 3.5 and 5.6 are themselves variables you would need to adjust accordingly (the final option of the three outlined would in effect become void)

Those using XL2007 & beyond would use COUNTIFS

Just to mention that you can put references instead of number (for example A1 and A2 instead of 3.4 and 5.1)

Another solution:

=SUMPRODUCT(--(ABS($D$1:$D$32-($A$1+$A$2)/2)<=($A$2-$A$1)/2))

comfirmed with ctrl+shift+enter

where A1 and A2 are references and D1:D32 is range