Countif to pur a persons age into selected caragories.

[chedd via OfficeKB.com]

Hi

I have been task to find a formula to be able to count peoples ages into
select different age groups for a quartely report. The age groups i have to
catagories them into is 18-21 , 22- 31, 32-41, 42-51, 52 -61 year olds
The example is in column A, but my spreadsheet contains over 126 for each
month.

Age

16
24
45
50
23
60

Please can you help with a formula to help catorgries the aboe ages into the
allocated groups.

Your help would be most appreciated.

 

[Vasant Nanavati]

Without using array formulas:

=COUNT(A:A)-COUNTIF(A:A,"<18")-COUNTIF(A:A,">21")

Etc.

______________________________________________________________________

 

[Ron Coderre]

Try something like this:

For 18 through 21
=SUM(COUNTIF(A:A,{"<18","<=21"})*{-1,1})

For 22 through 31
=SUM(COUNTIF(A:A,{"<22","<=31"})*{-1,1})

For 32 through 41
=SUM(COUNTIF(A:A,{"<32","<=41"})*{-1,1})
etc.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Teethless mama]

=SUMPRODUCT(--(A1:A100>=18),--(A1:A100<=21))
or
=COUNTIF(A:A,">=18")-COUNTIF(A:A,">=21")

 

[ShaneDevenshire]

Hi,

Here are four other approaches:

1. The shortest approach - use the FREQUENCY function: suppose your Ages
are in column A, starting in cell A2 and suppose you enter the number
21,31,41,51,61 in cells E5:E9. Then select the range F5:F9 and type the
formula
=FREQUENCY(A2:A26,E5:E9) but don't press Enter, instead
press Shift Ctrl Enter.

2. Use a pivot table:
Put your cursor in the Age column and create the pivot table. Put Age in
both the Row and Data areas! Change the Data calculation to Count. In the
pivot table put your cursor in the row area and choose PivotTable, Group and
Show Detail, Group and set the Start at to 12, the End at to 61 and the By to
10. There will be a lable in the row area 12-21, type over this with 18-21.

3. Using the same range as #1 enter the following formula in F5:
=COUNTIF(A$2:A$26,"<="&E5)-SUM(F$4:F4)
This formula assumes F4 is empty.

4. You can use the Data Analysis Add-in's Histogram tool.

 

[chedd via OfficeKB.com]

Fantastic and thank you for sparing the time to help.

Try something like this:

For 18 through 21
=SUM(COUNTIF(A:A,{"<18","<=21"})*{-1,1})

For 22 through 31
=SUM(COUNTIF(A:A,{"<22","<=31"})*{-1,1})

For 32 through 41
=SUM(COUNTIF(A:A,{"<32","<=41"})*{-1,1})
etc.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Hi

[quoted text clipped - 17 lines]

Your help would be most appreciated.