COUNTIF?

[CAW]

I have a column of data that I want to tally different age ranges from.

The first range checked uses this formula:

=COUNTIF($D$2:$D$41,"<35")

How do I check each range after that when the criteria is >35 & <40, >40 & <44, etc.....?

Thanks.

 

[MrActuary]

=COUNTIF(C1:C6,">35")-COUNTIF(C1:C6,">40")

 

[Zack Barresse]

Or for multiple criteria ...

=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2))

 

[yankee428]

you could use something like this

=SUM((rng>35)*(rng<40)+(rng>40)*(rng<44))

where you name your range rng. this is an array formula and must be entered using ctrl+shift+enter. notice that * equates to AND, and + equates to OR. be carefull not to overlap or your will doublecount some items.

 

[CAW]

Thanks for all the help guys! You resolved my issue! ray:

 

[yankee428]

Thanks Firefytr,

I'm sold on sumproduct() after doing some research on it. No more building array formulas using sum()

For anyone interested, here is a great article on this function and why they use -- in the formula. http://xldynamic.com/source/xld.SUMPRODUCT.html