Countifs with age ranges

[gbrook]

Greetings,

If I want to create a countifs with age ranges and this set of data how can I do it?

Im looking to create a countifs that captures males between 18-24, 25-34 35-44 etc in the same column. I need the formula to capture each range - thanks in advance for your help!

Cheers,
G

 

[Eric W]

Here's one way, depending on how your sheet looks:

Excel 2010

	A	B	C	D	E	F	G
Name	Sex	Age	Males
Al	M	18-24	25-34	35-44
Bob	M
Carrie	F
Don	M
Elaine	F
Frank	M
Gus	M
Hal	M
Irene	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]39[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]43[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=COUNTIFS($B$2:$B$10,"M",$C$2:$C$10,">=18",$C$2:$C$10,"<=24")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=COUNTIFS($B$2:$B$10,"M",$C$2:$C$10,">=25",$C$2:$C$10,"<=34")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]=COUNTIFS($B$2:$B$10,"M",$C$2:$C$10,">=35",$C$2:$C$10,"<=44")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[svendiamond]

=COUNTIFS(A:A,"<="&24,A:A,">="&18)

and so on and so forth

 

[gbrook]

Perfect thanks guys...late in the day brain is frozen!

 

[Eric W]

You're welcome.