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"

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



"Laura Mc" wrote:

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"

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Laura Mc" <Laura schrieb im Newsbeitrag
...
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"

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

"Peo Sjoblom" wrote:

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



"Laura Mc" wrote:

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"

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Laura Mc" schrieb im Newsbeitrag
...
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

"Peo Sjoblom" wrote:

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



"Laura Mc" wrote:

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"

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

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Laura Mc" schrieb im Newsbeitrag
...
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

"Peo Sjoblom" wrote:

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



"Laura Mc" wrote:

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"

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



"Laura Mc" wrote:

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

"Peo Sjoblom" wrote:

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



"Laura Mc" wrote:

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"

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



--
Regards
Frank Kabel
Frankfurt, Germany

"Laura Mc" schrieb im Newsbeitrag
...
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")

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Laura Mc" schrieb im
Newsbeitrag
...
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

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.

"Peo Sjoblom" wrote:

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



"Laura Mc" wrote:

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

"Peo Sjoblom" wrote:

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



"Laura Mc" wrote:

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"