Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif, Multiple criteria
Hello,
Can I use the countif or sumif functions with multiple criteria? For Example if I wanted to count every person in a selected range above the age of 16 and/or below the age of 4? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif, Multiple criteria
One way is to count all the people older than 4 and subtract the people older
than 16. =countif(a1:a999,""&4) - countif(a1:a999,""&16) You may want = in either/both of those comparisons. Another option would be to use: =sumproduct(--(a1:a9994),--(a1:a999<16)) =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Chris wrote: Hello, Can I use the countif or sumif functions with multiple criteria? For Example if I wanted to count every person in a selected range above the age of 16 and/or below the age of 4? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif, Multiple criteria
=COUNTIF($H$11:$H$22,""&16)+COUNTIF($H$11:$H$22," <"&4)
Reset Range to your range "Chris" wrote: Hello, Can I use the countif or sumif functions with multiple criteria? For Example if I wanted to count every person in a selected range above the age of 16 and/or below the age of 4? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif, Multiple criteria
COUNTIF(Range,"4") - COUNTIF(Range,"16") is not the same as
SUMPRODUCT(--(Range4),--(Range<16)) Besides, they count the wrong set. Valid formulas are =COUNTIF(Range,"<4")+COUNTIF(Range,"16") =COUNTA(Range)-SUMPRODUCT((Range=4)*(Range<=16)) =COUNTA(Range)-SUMPRODUCT(NOT(Range<4)*NOT(Range16)) =SUMPRODUCT((Range<4)*(Range<16))+SUMPRODUCT((Rang e4)*(Range16)) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif, Multiple criteria
You're right. I read the question incorrectly.
And I tried to make the point about what happens at the borders with my note about =. But I guess that I didn't make it well enough. Herbert Seidenberg wrote: COUNTIF(Range,"4") - COUNTIF(Range,"16") is not the same as SUMPRODUCT(--(Range4),--(Range<16)) Besides, they count the wrong set. Valid formulas are =COUNTIF(Range,"<4")+COUNTIF(Range,"16") =COUNTA(Range)-SUMPRODUCT((Range=4)*(Range<=16)) =COUNTA(Range)-SUMPRODUCT(NOT(Range<4)*NOT(Range16)) =SUMPRODUCT((Range<4)*(Range<16))+SUMPRODUCT((Rang e4)*(Range16)) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use multiple criteria with COUNTIF: between dates and not blank | Excel Worksheet Functions | |||
Countif using criteria in multiple columns | Excel Worksheet Functions | |||
Multiple Criteria using countif | Excel Worksheet Functions | |||
multiple criteria in one field 4a,4b etc of countif? | Excel Discussion (Misc queries) | |||
Multiple CountIf Criteria | Excel Worksheet Functions |