Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
Countif Multiple Criteria | Excel Worksheet Functions | |||
countif multiple criteria | Excel Worksheet Functions | |||
Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |