COUNTIF multiple criteria
 

I am having trouble with a formula that will count if two criteria are true,
so that I want it to count if the cell is between 100 and 500. Right now,
here is the formula I am using, but it does not work.

=COUNTIF(F309,"100")-COUNTIF(F309,"<500")

Any help would be greatly appreciated.

COUNTIF multiple criteria
 

Well, to correct your formula, change the <500 to 499 (assuming you want to
exclude the 100 and 500).
So:
=COUNTIF(F309,"100")-COUNTIF(F309,"499")
or....if it is just for a single cell, you could do the following:
=(F309100)*(F309<500) .... or =AND((F309100),(F309<500))
or....you could put it in an IF statement
=IF(AND(F309100,F309<500),1,0)

Hope this helps.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"SJC" wrote:

I am having trouble with a formula that will count if two criteria are true,
so that I want it to count if the cell is between 100 and 500. Right now,
here is the formula I am using, but it does not work.

=COUNTIF(F309,"100")-COUNTIF(F309,"<500")

Any help would be greatly appreciated.

COUNTIF multiple criteria
 

Thanks so much for sharing your expertise--the if statement did the trick.

"John C" wrote:

Well, to correct your formula, change the <500 to 499 (assuming you want to
exclude the 100 and 500).
So:
=COUNTIF(F309,"100")-COUNTIF(F309,"499")
or....if it is just for a single cell, you could do the following:
=(F309100)*(F309<500) .... or =AND((F309100),(F309<500))
or....you could put it in an IF statement
=IF(AND(F309100,F309<500),1,0)

Hope this helps.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"SJC" wrote:

I am having trouble with a formula that will count if two criteria are true,
so that I want it to count if the cell is between 100 and 500. Right now,
here is the formula I am using, but it does not work.

=COUNTIF(F309,"100")-COUNTIF(F309,"<500")

Any help would be greatly appreciated.

COUNTIF multiple criteria
 

Try it like this:

=--AND(F309100,F309<500)

count if the cell is between 100 and 500

99 times out of 100 when someone says that what they really mean is greater
than or equal to 100 and less than or equal to 500. So, just in case:

=--AND(F309=100,F309<=500)

--
Biff
Microsoft Excel MVP


"SJC" wrote in message
...
I am having trouble with a formula that will count if two criteria are
true,
so that I want it to count if the cell is between 100 and 500. Right now,
here is the formula I am using, but it does not work.

=COUNTIF(F309,"100")-COUNTIF(F309,"<500")

Any help would be greatly appreciated.