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"