Help with a COUNTIF, Please

[Guest]

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

 

[Vasant Nanavati]

COUNTIF doesn't play well with AND. Try:

=SUMPRODUCT((I4:I450>=0)*(I4:I450<=10))

 

[KL]

Hi,

You can also try this:

=COUNTIF(I4:I450,"<=10")-COUNTIF(I4:I450,"<0")

Regards,
KL

 

[Guest]

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?

 

[KL]

Nope, it just counts empty cells as 0-values. Try this:

=SUMPRODUCT((I4:I450>=0)*(I4:I450<=10)*(I4:I450<>""))

KL

 

[Vasant Nanavati]

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.

 

[Guest]

BRILLIANT!

This worked like a charm. I had to add a third term to the other nine
equations, but it worked. Thanks very much.

 

[KL]

Hi again,

As far as I know -- has nothing to do with summing or counting. In
SUMPRODUCT conditional counts or summs it is just used to coerce the logical
values TRUE and FALSE into their numerical equivalents 1 and 0. It is
usually necessary when there is only one condition and no mathematical
operations, for example:

=SUMPRODUCT((I4:I450>=0)*(I4:I450<=10))
-- not necessary as multiplication operator * coerces the logical values in
the matrices resulting from the equation to 1 or 0

=SUMPRODUCT(--(I4:I450<=10))
-- necessary

Regards,
KL

 

[KL]

Well, I did test it and it counts 'em all

KL

 

[Don Guillett]

try changing >=0 to >0

 

[Vasant Nanavati]

Good point; missed that!

--

Vasant

try changing >=0 to >0

--
Don Guillett
SalesAid Software
(e-mail address removed)
for

 

[KL]

My only doubt is what if the OP wants to count both 0 values and 0-10, but
not empty cells?

KL

 

[Vasant Nanavati]

Oh, you are absolutely correct. I meant that I missed the fact that it was

 

[Ron Rosenfeld]

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

You could also use the FREQUENCY worksheet function.

Select 10 cells.
Click in the formula bar.
Enter the following formula into the bar.

=FREQUENCY($I$4:$I$450,{10,20,30,40,50,60,70,80,90,100})

Then, instead of hitting <enter>, hold down <ctrl><shift> while hitting
<enter>.

This will be entered as an array formula and you will have the output you are
looking for.


--ron