Count of Number Values Greater Than, Less Than

A

Anne

How do you perform this count on a range of cells?

CountIf and SumProduct are both returning the wrong total, in more than one
column.
 
D

David Billigmeier

These should work...
=COUNTIF(A1:A10,">=7")
=SUMPRODUCT(--(A1:A10>=7))

If those don't work your values might be formatted as text, in which case try:
=SUMPRODUCT(--(--A1:A10>=7))
 
A

Anne

Thanks, but I need >=0 AND <=36. It's the AND part that screws it all up.

=COUNTIF(D2:D9,">=0")-COUNTIF(D2:D9,"=<36") returns the wrong value. So
does =SUMPRODUCT(--(D2:D9>=0)--(D2:D9<=36))

Values are all formated as numbers. I can't figure out what it's counting.

I just discovered that =SUM(IF((D2:D9>0)*(D2:D9<=36),1,0)) using
ctrl+shift+enter works. I'm grateful that it does, but why does it perform a
count when the function is to sum?

:
 
J

JNW

maybe
=countif(d2:d9,">=0")-countif(d2:d9,">36")
This way you are counting all values >=0 and subtracting those values that
are greater then the range you don't want to include.
 
P

PCLIVE

Oops! I forgot about the greater than or eaqual to:

=SUMPRODUCT(--(D2:D9>=0),--(D2:D9<37))
 
M

M. Authement

To answer your question of why SUM(IF... counts:

Starting with the IF((D2:D9>0)*(D2:D9<=36), these two conditional checks
return arrays of 1 and 0 (actually, they return TRUE and FALSE which are
then coerced into 1 and 0 through the multiplication). When multiplied,
these two arrays create one array of 1s and 0s.

So now you have something like IF({1,1,0,1,0,0,0,1}...or whatever the
results would actually be. Each of these elements are evaluated such as
IF(1,1,0), IF(1,1,0), IF(0,1,0), etc. resulting in a further array of 1s and
0s. This final array is then summed, giving results that mimick a
COUNT-type function.

If you want to see this in action select the cell, then go to Tools,Formula
Auditing, Evaluate Formula.
 
Top