Count the ages within age range in excel

T

Tarek_CTG

Board Regular
Joined
Apr 27, 2015
Messages
160
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I have an excel file where in 'A' column there are some ages. The format of age is like this: 30 Y 7 M (means 30 years 7 months). Now I need the total count of ages (how many ages) ranges between 18-24 years in column B, 25-34 years in column C, 35-44 years in Column D, and more than 44 years n Column E. What formula should I use in B, C, D and E column?
Thanks in advance.

Age18-24 yrs25-34 yrs35-44 yrsMore than 44 yrs
30 Y 7 M
39 Y 6 M
29 Y 5 M
23 Y 8 M
29 Y 5 M
25 Y 4 M
24 Y 11 M
28 Y 4 M
29 Y 2 M
29 Y 1 M
20 Y 7 M
21 Y 0 M
18 Y 4 M
24 Y 0 M
20 Y 11 M
21 Y 8 M
22 Y 11 M
26 Y 0 M
25 Y 10 M
30 Y 6 M
30 Y 8 M
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
There could be better ways of doing it, if you would have updated your current Excel Version and platform on your profile. Moreover prefer uploading sample data using XL2BB which eliminates effort to re-create sample data.

Yet check this -

Book2
ABCDE
191110
2Age18-24 yrs25-34 yrs35-44 yrsMore than 44 yrs
330 Y 7 M0100
439 Y 6 M0010
529 Y 5 M0100
623 Y 8 M1000
729 Y 5 M0100
825 Y 4 M0100
924 Y 11 M1000
1028 Y 4 M0100
1129 Y 2 M0100
1229 Y 1 M0100
1320 Y 7 M1000
1421 Y 0 M1000
1518 Y 4 M1000
1624 Y 0 M1000
1720 Y 11 M1000
1821 Y 8 M1000
1922 Y 11 M1000
2026 Y 0 M0100
2125 Y 10 M0100
2230 Y 6 M0100
2330 Y 8 M0100
Sheet1
Cell Formulas
RangeFormula
B1:E1B1=SUM(B3:B23)
B3:B23B3=IFS(LEFT(A3,2)*1<=24,1,TRUE,0)
C3:C23C3=IFS(AND(LEFT(A3,2)*1>=25,LEFT(A3,2)*1<=34),1,TRUE,0)
D3:D23D3=IFS(AND(LEFT(A3,2)*1>=35,LEFT(A3,2)*1<=44),1,TRUE,0)
E3:E23E3=IFS(LEFT(A3,2)*1>=44,1,TRUE,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:E23Cell Value=1textNO
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
SanjayGMusafir said:
There could be better ways of doing it, if you would have updated your current Excel Version and platform on your profile. Moreover prefer uploading sample data using XL2BB which eliminates effort to re-create sample data.

Yet check this -

Book2
ABCDE
191110
2Age18-24 yrs25-34 yrs35-44 yrsMore than 44 yrs
330 Y 7 M0100
439 Y 6 M0010
529 Y 5 M0100
623 Y 8 M1000
729 Y 5 M0100
825 Y 4 M0100
924 Y 11 M1000
1028 Y 4 M0100
1129 Y 2 M0100
1229 Y 1 M0100
1320 Y 7 M1000
1421 Y 0 M1000
1518 Y 4 M1000
1624 Y 0 M1000
1720 Y 11 M1000
1821 Y 8 M1000
1922 Y 11 M1000
2026 Y 0 M0100
2125 Y 10 M0100
2230 Y 6 M0100
2330 Y 8 M0100
Sheet1
Cell Formulas
RangeFormula
B1:E1B1=SUM(B3:B23)
B3:B23B3=IFS(LEFT(A3,2)*1<=24,1,TRUE,0)
C3:C23C3=IFS(AND(LEFT(A3,2)*1>=25,LEFT(A3,2)*1<=34),1,TRUE,0)
D3:D23D3=IFS(AND(LEFT(A3,2)*1>=35,LEFT(A3,2)*1<=44),1,TRUE,0)
E3:E23E3=IFS(LEFT(A3,2)*1>=44,1,TRUE,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:E23Cell Value=1textNO
Click to expand...
actually I need total sum in B3, C3, D3, E3 cells, for example: data will be shown as like this, B3=9, C3=11, D3=1, E3=0
 
Upvote 0
SanjayGMusafir said:
There could be better ways of doing it, if you would have updated your current Excel Version and platform on your profile. Moreover prefer uploading sample data using XL2BB which eliminates effort to re-create sample data.

Yet check this -

Book2
ABCDE
191110
2Age18-24 yrs25-34 yrs35-44 yrsMore than 44 yrs
330 Y 7 M0100
439 Y 6 M0010
529 Y 5 M0100
623 Y 8 M1000
729 Y 5 M0100
825 Y 4 M0100
924 Y 11 M1000
1028 Y 4 M0100
1129 Y 2 M0100
1229 Y 1 M0100
1320 Y 7 M1000
1421 Y 0 M1000
1518 Y 4 M1000
1624 Y 0 M1000
1720 Y 11 M1000
1821 Y 8 M1000
1922 Y 11 M1000
2026 Y 0 M0100
2125 Y 10 M0100
2230 Y 6 M0100
2330 Y 8 M0100
Sheet1
Cell Formulas
RangeFormula
B1:E1B1=SUM(B3:B23)
B3:B23B3=IFS(LEFT(A3,2)*1<=24,1,TRUE,0)
C3:C23C3=IFS(AND(LEFT(A3,2)*1>=25,LEFT(A3,2)*1<=34),1,TRUE,0)
D3:D23D3=IFS(AND(LEFT(A3,2)*1>=35,LEFT(A3,2)*1<=44),1,TRUE,0)
E3:E23E3=IFS(LEFT(A3,2)*1>=44,1,TRUE,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:E23Cell Value=1textNO
Click to expand...
Book1
ABCDE
1Age18-24 yrs25-34 yrs 35-44 yrs More than 44 yrs
219 Y 9 M
324 Y 6 M
422 Y 7 M
526 Y 8 M
631 Y 1 M
721 Y 5 M
820 Y 0 M
940 Y 0 M
1021 Y 3 M
1158 Y 10 M
1219 Y 9 M
1328 Y 9 M
1418 Y 4 M
1538 Y 9 M
1621 Y 7 M
1725 Y 3 M
1824 Y 5 M
Sheet1
 
Upvote 0
Check this -

Book1
ABCDE
1Age18-24 yrs25-34 yrs 35-44 yrs More than 44 yrs
219 Y 9 M8621
324 Y 6 M
422 Y 7 M
526 Y 8 M
631 Y 1 M
721 Y 5 M
820 Y 0 M
940 Y 0 M
1021 Y 3 M
1158 Y 10 M
1219 Y 9 M
1328 Y 9 M
1418 Y 4 M
1538 Y 9 M
1621 Y 7 M
1725 Y 3 M
1824 Y 5 M
Sheet1
Cell Formulas
RangeFormula
B2B2=SUM((LEFT(A2:A18,2)*1<24)*1)
C2C2=SUM((LEFT(A2:A18,2)*1<34)*1)-B2
D2D2=SUM((LEFT(A2:A18,2)*1<44)*1)-SUM(B2:C2)
E2E2=SUM((LEFT(A2:A18,2)*1>44)*1)
 
Upvote 0
SanjayGMusafir said:
Check this -

Book1
ABCDE
1Age18-24 yrs25-34 yrs 35-44 yrs More than 44 yrs
219 Y 9 M8621
324 Y 6 M
422 Y 7 M
526 Y 8 M
631 Y 1 M
721 Y 5 M
820 Y 0 M
940 Y 0 M
1021 Y 3 M
1158 Y 10 M
1219 Y 9 M
1328 Y 9 M
1418 Y 4 M
1538 Y 9 M
1621 Y 7 M
1725 Y 3 M
1824 Y 5 M
Sheet1
Cell Formulas
RangeFormula
B2B2=SUM((LEFT(A2:A18,2)*1<24)*1)
C2C2=SUM((LEFT(A2:A18,2)*1<34)*1)-B2
D2D2=SUM((LEFT(A2:A18,2)*1<44)*1)-SUM(B2:C2)
E2E2=SUM((LEFT(A2:A18,2)*1>44)*1)
Click to expand...
Thank you very much, Its working indeed. Just one more thing, instead of subtract B2 or sum of B2:C2 as like this:
=SUM((LEFT(A2:A18,2)*1<34)*1)-B2
is it possible to use direct formula like you mentioned before:
=IFS(AND(LEFT(A3,2)*1>=25,LEFT(A3,2)*1<=34),1,TRUE,0)
without depending on previous sum?

Thanks in advance again
 
Upvote 0
How about
Fluff.xlsm
ABCDEF
1Age18-24 yrs25-34 yrs35-44 yrsMore than 44 yrs
230 Y 7 M91110
339 Y 6 M
429 Y 5 M
523 Y 8 M
629 Y 5 M
725 Y 4 M
824 Y 11 M
928 Y 4 M
1029 Y 2 M
1129 Y 1 M
1220 Y 7 M
1321 Y 0 M
1418 Y 4 M
1524 Y 0 M
1620 Y 11 M
1721 Y 8 M
1822 Y 11 M
1926 Y 0 M
2025 Y 10 M
2130 Y 6 M
2230 Y 8 M
23
Sheet6
Cell Formulas
RangeFormula
B2B2=COUNTIFS(A2:A100,">=18 *",A2:A100,"<=24*")
C2C2=COUNTIFS(A2:A100,">=25 *",A2:A100,"<=34*")
D2D2=COUNTIFS(A2:A100,">=35 *",A2:A100,"<=44*")
E2E2=COUNTIFS(A2:A100,">=44 *")
 
Upvote 0
Tarek_CTG said:
Thank you very much, Its working indeed. Just one more thing, instead of subtract B2 or sum of B2:C2 as like this:
=SUM((LEFT(A2:A18,2)*1<34)*1)-B2
is it possible to use direct formula like you mentioned before:
=IFS(AND(LEFT(A3,2)*1>=25,LEFT(A3,2)*1<=34),1,TRUE,0)
without depending on previous sum?

Thanks in advance again
Click to expand...
You can do that or way @Fluff suggested. Both shall work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R
H
  • Solved
Reduce size of (or speed up) excel spreadsheet
Replies
5
Views
88
helpcharity
H
K
  • Question Question
conditional formatting question
Replies
2
Views
585
kerm007
K
J
  • Solved
#N/A Error
2
Replies
14
Views
354
Jedi Master
J
R
  • Solved
Display age range and age based on date entered and actual calendar date.
Replies
6
Views
348
dreid1011
dreid1011

Forum statistics

Threads
1,223,882
Messages
6,175,166
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top