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

Starting with the IF((D2:D90)*(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.

"Anne" wrote in message
...
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:D90)*(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?

"David Billigmeier" wrote:


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

--
Regards,
Dave


"Anne" wrote:

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.