COUNTIF

[Guest]

I'm trying to count the number of cells that meets both of two arguments.
When I consulted Excel 2003 HELP, it suggested that I use
=COUNTIF(B1:B3,">=32")-COUNTIF(B1:B3,">85") With the description "Number of
cells with a value greater than or equal to "A" and less than or equal to "B"

But what it actually returns is the DIFFERENCE between A & B (see the minus
sign between the two arguments?) Obviously I need a formula that would return
a count of the number of cells in a range of multiple cells that meets BOTH
greater than "A" AND less than "B"

 

[Guest]

No, it does not return the difference, it returns the numbers that are
greater than or equal to 32 and less than or equal to 85. Think about it, the
first part returns ALL numbers greater than or qual to 32, now to get the
numbers less than or equal to 85 you must subtract all numbers greaten than
83. This formula which I am sure looks logically better to you returns the
same result

=SUMPRODUCT(--(B1:B3>=32),--(B1:B3<=85))

having said that, why didn't you test the formula first, using a small range
like B1:B3 it should be easy to see it works the way it's supposed to

Regards,

Peo Sjoblom

 

[Frank Kabel]

Hi
but this is just what the formula does?
Have you tried it?

 

[Guest]

This is my first time to use this discussion group and I'm shocked to see a
response written such as yours was. I suppose my expectations could have
been too high. Of course I tested it first. I tried and received the
following results. I believe I would benefit most if you could just explain
where I went wrong in more detail and with

Example
cell1 = 500
cell2 = 90
cell3 = 123

WHAT EXCEL RETURNS USING THE FORMULA
0-99 2 (3 are >0 minus 1 is <99)
100-199 0 (2 are >100 minus 2 are <199)
200-299 -1 (1 is >200 minus 2 are <299) 200-299
300-399 -1 (1 is >300 minus 2 are <399) 300-399
400-499 -1 (1 is >400 minus 2 are <499) 400-499
over 500 1 (1 is >500)
TOTAL 0

 

[Frank Kabel]

Hi Laura
could you post for your two first examples the EXACT formulas you have
used as I got the following results
0-99: 1 for the formula: =COUNTIF(A1:A3,">=0")-COUNTIF(A1:A3,">99")
100-199: 1 for the formula:
=COUNTIF(A1:A3,">=100")-COUNTIF(A1:A3,">199")

So either something is different in your formulas or both Peo and I are
missing something in your explanation

 

[Guest]

Sure. I'm trying to count the cells BETWEEN two numbers (i.e., 0-99) and
you've shown it as >0 and >99 and it should be >0 and <99. I just copied &
pasted the formula that EXCEL Help suggested to be sure I did not type it
incorrectly. Here it is again: =COUNTIF(B1:B12,">=0")-COUNTIF(B1:B12,"<=99")

 

[Guest]

First of all I apologize if I shocked you.
I explained how the formula works and it still does
Using B1:B3 here are the results:

COUNTIF(B1:B3,">=0")-COUNTIF(B1:B3,">=99") 1
COUNTIF(B1:B3,">=100")-COUNTIF(B1:B3,">=199") 1
COUNTIF(B1:B3,">=200")-COUNTIF(B1:B3,">=299") 0
COUNTIF(B1:B3,">=300")-COUNTIF(B1:B3,">=399") 0
COUNTIF(B1:B3,">=400")-COUNTIF(B1:B3,">=499") 0
COUNTIF(B1:B3,">=500") 1

Which is correct, I have no idea where you get your results from, how does
the formula look that will return 2 for 0-99? As you can see mine returns 1
What excel says regarding 0-99 is 3 greater than or equal to zero minus 2
greater than equal to 99 Where in the formula do you have <=90? The whole
point which you protested was that you use

=0 - >=99 NOT >=0 - <=99

What it seemt to me that you did was that you changed the formula you first
posted because you thought it was wrong then you say you get the wrong
results from a formula that differs from the one you posted
Please post the formula you used to get those results

Regards,

Peo Sjoblom

 

[Frank Kabel]

Hi Laura
but why not use the formulas as I provided it to you.
Your formula
=COUNTIF(B1:B12,">=0")-COUNTIF(B1:B12,"<=99")
of course won't work.
Use:
=COUNTIF(B1:B12,">=0")-COUNTIF(B1:B12,">99")

To explain it:
The first part: COUNTIF(B1:B12,">=0") count ALL occurenced there B1:B12
is >=0. So this includes also all cells which are larger than 99. So
the first part returns too many matches. To correct this you have to
SUBTRACT the amount of occurences which are >99. This is what the
second part COUNTIF(B1:B12,">99") does.

To give you an example
B1: 90
B2: 99
B3: 120

So your expected result should be two. Now lets evaluate what both
formula parts return:
1. =COUNTIF(B1:B3,">=0") -> This of course returns 3. So it also
counts the value 120 as match.
2. =COUNTIF(B1:B3,">99") -> and this returns 1 (the 120 value).

So the final result is 3-1 = 2 (and this would be the expected result).
So the above formula count the values BETWEEN these numbers. And as Peo
in his first post suggested if this logic is not that obvious for you
you may try an alternative solution:
=SUMPRODUCT(--(B1:B12>=0),--(B1:B12<=99))

This will return 2 as well.

Post back if you still have questions but PLEASE try the exact
suggestions we provided to you

 

[Guest]

Obviously, I somehow changed the second half to "<" instead of ">" as Excel
HELP originally intended. And our previous discussions regarding my attempt
to find the the number of cells greater than "A" and less than "B" have only
contributed to that confusion. I now see the error.