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?

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

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?

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

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