How to sort and count children by age

jonwondon

New Member
Joined
Oct 20, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I work at a preschool and we're doing a summer camp ranging ages 5-11. I have a list of 80 kids with their birthdays. I've created a formula that tells me their age based on date of birth.

This list is ever changing and I wanted to make a formula that will divide and count the kids into 4 age groups and look something like this:

5-6:
7-8:
9-10:
11:

Any help would be greatly appreciated.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,338
Office Version
  1. 365
Platform
  1. Windows
Using a lookup formula and a pivot table.

Book1
ABCDEFGHIJ
1NameDOBAGEGroupAgeGroupGroupName
2 Jonathan9/12/20128.697-855-65-6
3 Ronny3/12/20129.199-1077-8 Anayah
4 Julia5/18/20147.017-899-10 Arwa
5 Carla6/15/201010.939-101111 Emmie
6 Fox4/20/201011.0911 Ethan
7 Theodor9/10/201010.699-10 Madeleine
8 Diyan1/5/201011.3711 Rahma
9 Anayah2/8/20156.285-67-8
10 Frankie2/20/201011.2511 Ayan
11 Michelle6/29/20137.897-8 Forrest
12 Kiyan12/3/20137.467-8 Frazer
13 Noor8/9/20128.787-8 Jannat
14 Rahma8/25/20155.745-6 Jonathan
15 Arwa3/5/20165.215-6 Julia
16 Frazer5/13/20138.027-8 Kiyan
17 Rosemary7/13/201010.869-10 Michelle
18 Jannat5/28/20137.987-8 Noor
19 Emmie6/12/20146.945-69-10
20 Cassius2/9/201110.289-10 Carla
21 Ethan12/9/20146.445-6 Cassius
22 Forrest5/2/20138.057-8 Edmund
23 Ayan11/25/20137.487-8 Kit
24 Kit4/16/201110.109-10 Ronny
25 Madeleine1/29/20156.315-6 Rosemary
26 Edmund4/29/201110.069-10 Theodor
2711
28 Diyan
29 Fox
30 Frankie
31Grand Total
Sheet1
Cell Formulas
RangeFormula
A2:A26A2=PROPER(qnm(25))
B2:B26B2=RANDDATE(2010,2016)
C2:C26C2=(NOW()-B2)/365
D2:D26D2=LOOKUP(C2,$F$2:$F$5,$G$2:$G$5)
Dynamic array formulas.
 

jonwondon

New Member
Joined
Oct 20, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
My current solution is just to do it by single didget
Using a lookup formula and a pivot table.

Book1
ABCDEFGHIJ
1NameDOBAGEGroupAgeGroupGroupName
2 Jonathan9/12/20128.697-855-65-6
3 Ronny3/12/20129.199-1077-8 Anayah
4 Julia5/18/20147.017-899-10 Arwa
5 Carla6/15/201010.939-101111 Emmie
6 Fox4/20/201011.0911 Ethan
7 Theodor9/10/201010.699-10 Madeleine
8 Diyan1/5/201011.3711 Rahma
9 Anayah2/8/20156.285-67-8
10 Frankie2/20/201011.2511 Ayan
11 Michelle6/29/20137.897-8 Forrest
12 Kiyan12/3/20137.467-8 Frazer
13 Noor8/9/20128.787-8 Jannat
14 Rahma8/25/20155.745-6 Jonathan
15 Arwa3/5/20165.215-6 Julia
16 Frazer5/13/20138.027-8 Kiyan
17 Rosemary7/13/201010.869-10 Michelle
18 Jannat5/28/20137.987-8 Noor
19 Emmie6/12/20146.945-69-10
20 Cassius2/9/201110.289-10 Carla
21 Ethan12/9/20146.445-6 Cassius
22 Forrest5/2/20138.057-8 Edmund
23 Ayan11/25/20137.487-8 Kit
24 Kit4/16/201110.109-10 Ronny
25 Madeleine1/29/20156.315-6 Rosemary
26 Edmund4/29/201110.069-10 Theodor
2711
28 Diyan
29 Fox
30 Frankie
31Grand Total
Sheet1
Cell Formulas
RangeFormula
A2:A26A2=PROPER(qnm(25))
B2:B26B2=RANDDATE(2010,2016)
C2:C26C2=(NOW()-B2)/365
D2:D26D2=LOOKUP(C2,$F$2:$F$5,$G$2:$G$5)
Dynamic array formulas.

Well right now I'm using:

=DATEDIF(D2,TODAY(),"Y") & " Y, " & DATEDIF(D2,TODAY(),"YM") & " M "

To display their Age with month - which puts out something like:

DOB: 10/17/2012
AGE: 8 Y, 7 M

So I'm simply using:

=COUNTIF(E2:E51,"8 Y*")

To count all 8 year olds....etc


How do I include TWO phrases to count? So that I may count "7 Y" & "8 Y" into one category?
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,338
Office Version
  1. 365
Platform
  1. Windows
I wouldn't use 'DatedIF'. You can get the same effect with a custom number format like this.

Cell Formulas
RangeFormula
A2:A26A2=PROPER(qnm(25))
B2:B26B2=RANDDATE(2009,2015)
C2:C26C2=(NOW()-B2)
D2:D26D2=LOOKUP(C2/365,$F$2:$F$5,$G$2:$G$5)
Dynamic array formulas.


Where column C has a custom number format of 'yy\Y m\M'.

But, you could do it with a formula like this I guess, but I'm not a fan because it's going to be a pain to write a formula like this for each group.

Book1
QRS
18Y 7M2
27Y 9M
Sheet1
Cell Formulas
RangeFormula
S1S1=SUMPRODUCT((LEFT(Q1:Q2,2)="8Y")+(LEFT(Q1:Q2,2)="7Y"))
 

jonwondon

New Member
Joined
Oct 20, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I wouldn't use 'DatedIF'. You can get the same effect with a custom number format like this.

Cell Formulas
RangeFormula
A2:A26A2=PROPER(qnm(25))
B2:B26B2=RANDDATE(2009,2015)
C2:C26C2=(NOW()-B2)
D2:D26D2=LOOKUP(C2/365,$F$2:$F$5,$G$2:$G$5)
Dynamic array formulas.


Where column C has a custom number format of 'yy\Y m\M'.

But, you could do it with a formula like this I guess, but I'm not a fan because it's going to be a pain to write a formula like this for each group.

Book1
QRS
18Y 7M2
27Y 9M
Sheet1
Cell Formulas
RangeFormula
S1S1=SUMPRODUCT((LEFT(Q1:Q2,2)="8Y")+(LEFT(Q1:Q2,2)="7Y"))

Your code and work looks great,

However, it seems a bit overly complex. Your code is probably the best way, by far, but I might be sharing this document with others and I cannot always access this thread.

My original thought was:

How do I include TWO phrases to count? So that I may count "7 Y" & "8 Y" into one category?

using

=COUNTIF(E2:E51,"8 Y*")


perhaps something along the lines of

=COUNTIF(E2:E51,"7 Y*" OR "8 Y*")

but that is obviously not working, what would be the working function?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,936
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=SUM(COUNTIF(E2:E51,{"7 Y*","8 Y*"}))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,936
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

jonwondon

New Member
Joined
Oct 20, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
Glad we could help & thanks for the feedback.

The original code is incredible and definitely the more professional route. But again, I work with a team of people who "kinda" know excel - so I know the final function you provided will help them understand better.

Thank you again!
 

Forum statistics

Threads
1,148,140
Messages
5,745,035
Members
423,917
Latest member
Frank1931

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
Top