Countif to pur a persons age into selected caragories.

  • Thread starter chedd via OfficeKB.com
  • Start date
C

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.
 
V

Vasant Nanavati

Without using array formulas:

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

Etc.

______________________________________________________________________
 
R

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
 
T

Teethless mama

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

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.
 
C

chedd via OfficeKB.com

Fantastic and thank you for sparing the time to help.

Ron said:
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
[quoted text clipped - 17 lines]
Your help would be most appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top