Countif, Multiple criteria

[Guest]

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]

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:a999>4),--(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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Guest]

=COUNTIF($H$11:$H$22,">"&16)+COUNTIF($H$11:$H$22,"<"&4)
Reset Range to your range

 

[Herbert Seidenberg]

COUNTIF(Range,">4") - COUNTIF(Range,">16") is not the same as
SUMPRODUCT(--(Range>4),--(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(Range>16))
=SUMPRODUCT((Range<4)*(Range<16))+SUMPRODUCT((Range>4)*(Range>16))

 

[Dave Peterson]

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.