Well, I did test it and it counts 'em all :-)
KL
"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
I think it should work without the third criterion, since the first one
(=0) would ignore blank cells. Didn't test it, though. as it seemed
straightforward enough.
--
Vasant
"KL" wrote in message
...
Nope, it just counts empty cells as 0-values. Try this:
=SUMPRODUCT((I4:I450=0)*(I4:I450<=10)*(I4:I450<" "))
KL
"Marty" wrote in message
...
Thanks for the response, but it didn't work. Result is 431. Looks
like
it
has "summed the product" instead of counting the cells.
I seem to recall someone writing about a double negative (--)somehow
being
used in the SUMPRODUCT function to trick it into counting rather than
multiplying or summing.
Any other ideas?
"Vasant Nanavati" wrote:
COUNTIF doesn't play well with AND. Try:
=SUMPRODUCT((I4:I450=0)*(I4:I450<=10))
--
Vasant
"Marty" wrote in message
...
Hey all:
This seems like such a simple problem, but I've been staring at it
for
a
while and can't figure out what I'm doing wrong.
I have numbers ranging from 0 to 100 in cells I4 to I450 in a
worksheet.
I
want to count the cells by increments of 10. In other words, I need
10
formulas showing:
-how many numbers are greater than zero and less than or equal to
10,
-how many are greater than 10 but less than or equal to 20,
-greater than 20 but less than or equal to 30,
and so forth.
Here is the formula I put into the cell to calculate the =0 and <=
10
range:
=COUNTIF(I4:I450,AND(I4:I450=0,I4:I450<=10))
This yields a zero. I know the answer should be 168.
Can somebody please tell me what's wrong with this formula?
Thanks,
MARTY
|