Help with a COUNTIF, Please
 

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

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

Hi,

You can also try this:

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

Regards,
KL


"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

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

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

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

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" wrote:

Hi,

You can also try this:

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

Regards,
KL


"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

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

"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

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

try changing =0 to 0

--
Don Guillett
SalesAid Software

"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

Good point; missed that! :-)

--

Vasant

"Don Guillett" wrote in message
...
try changing =0 to 0

--
Don Guillett
SalesAid Software

"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

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

KL

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Good point; missed that! :-)

--

Vasant

"Don Guillett" wrote in message
...
try changing =0 to 0

--
Don Guillett
SalesAid Software

"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

Oh, you are absolutely correct. I meant that I missed the fact that it was
=0 and not 0. Your solution was the best.

--

Vasant



"KL" wrote in message
...
My only doubt is what if the OP wants to count both 0 values and 0-10, but
not empty cells?

KL

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Good point; missed that! :-)

--

Vasant

"Don Guillett" wrote in message
...
try changing =0 to 0

--
Don Guillett
SalesAid Software

"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

On Sun, 22 May 2005 14:30:03 -0700, "Marty"
wrote:

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