Group Ages by DOB

[birchau]

Hey All,
First time posting. I have a problem with a table I'm trying to create. I have Age Groups as listed below, and I have a list of DOBs in another table. I've been trying to use a COUNTIF but can't seem to get the age range right for each cell in the Age Groups table. Any help?


[TABLE="width: 228"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]DOB[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]6/03/2003[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]5/08/1979[/TD]
[/TR]
[TR]
[TD]Client C[/TD]
[TD]3/10/2017[/TD]
[/TR]
[TR]
[TD]Client D[/TD]
[TD]19/06/2004[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 250"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Age[/TD]
[TD]No. Clients[/TD]
[/TR]
[TR]
[TD]0-4 Years[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]5-11 Years[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]12-17 Years[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Over 18 Years[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Age unknown[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[palaeontology]

if you're happy to have a helper cell, then you could do the following ...

if client data is in range J5:K9, then in L6 enter .... =DATEDIF(K6,TODAY(),"d")/365 and drag down to L9

if age group table is in the range N5:O11,

then in O6 (beside category 0-4 years) enter ... =COUNTIF(L6:L9,"<4")

in O7 enter ... =COUNTIFS(L6:L9,">4",L6:L9,"<12")

in O8 enter ... =COUNTIFS(L6:L9,">11",L6:L9,"<18")

in O9 enter ... =COUNTIF(L6:L9,">17")

clunky, but should do the job

Kind regards,

Chris