I would appreciate some assistance with developing a formula for identifying the number of clients within certain age ranges. I have an excel database with several tables in it (outlined below). Within the ClientTbl, there is static info such as Last Name, First Name, Sex, Date of Birth, Etc… Within the CourtTbl, is the client’s Arrest Date, Court Date, Charge, Etc… I have put examples of both below. I would like to count the number of people in each of a given age range based on the client’s Date of Birth and their Arrest date. For Example:

How many less than 18 years old,

how many between 18 and 28 years old,

how many between 29 and 38 years old,

how many between 39 and 48 years old,

How many between 49 and 58 years old and

How many older than 58.ClientTbl: Example Data

A B C D E 1 ID LastName FirstName Sex DateOfbirth 2 HT345 Doe John Male 12/05/1974 3 TR897 Smith Laura Female 11/11/1964 4 YT231 Johnson Tim Male 10/02/1984 5 UP674 Dane Sarah Female 11/23/1994CourtTbl: Example Data

A B C D 1 ID ArrestDate CourtDate Charge 2 HT345 02/03/2018 02/04/2018 DUI 3 TR897 02/03/2018 02/04/2018 Domestic Battery 4 YT231 02/03/2018 02/04/2018 DUI 5 UP674 02/03/2018 02/04/2018 DUII am having trouble getting a COUNTIF to work correctly. Thank you in advance for your assistance.

message edited by Haus

✔ Best Answer

I had hoped to avoid creating additional fields

You could always hide the Age column if you do not wish to have it displayed.

just use a formula to get the desired information.OK, here are SUMPRODUCT() formulas that should get you what you want:

With your sheet like:

A B 11) 12) less than 18 13) between 18 and 28 14) between 29 and 38 15) between 39 and 48 16) between 49 and 58 17) older than 58To get your count:

In cell B12 enter the formula: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)<18))

In cell B13: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)>=18),--(YEAR(TODAY())-YEAR($D$2:$D$6)<=28))

In cell B14: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)>=29),--(YEAR(TODAY())-YEAR($D$2:$D$6)<=38))

In cell B15: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)>=39),--(YEAR(TODAY())-YEAR($D$2:$D$6)<=48))

In cell B16: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)>=49),--(YEAR(TODAY())-YEAR($D$2:$D$6)<=58))

In cell B17: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)>58))

See how that works for you.

MIKE

Haus, As I mentioned in your last thread, Excel questions are best asked in the Office Software forum.

I currently do not have access to Excel, but will post a solution later today, provided someone else does not jump in with one.

MIKE

message edited by mmcconaghy

Haus, The simplest way that I can think of is to first get the ages of each client, then do a count,

With your ClientTbl sheet like:A B C D E F 1) ID LastName FirstName DateOfBirth Sex Charge 2) NH1389 Doe John 12/5/1974 Male Domestic Battery 3) JU345 Smith Terry 2/21/1986 Male DUI 4) TY6745 Johnson Tim 3/1/1969 Male Domestic Battery 5) IU987 Doe Jane 7/1/1978 Female Domestic Battery 6) WE345 Greer Nancy 4/15/1978 Female Retail Theft 7) 8) 9) DB Male 2 10) DB Female 1 11) 12) less than 18 13) between 18 and 28 14) between 29 and 38 15) between 39 and 48 16) between 49 and 58 17) older than 58First get their age:

In cell G2 enter the formula: =(TODAY()-D2)/365.25

This will give you the age in Years only

Drag down five rowsNext get your count:

In cell B12 enter the formula: =COUNTIF($G$2:$G$6,"<"&18)

In cell B13: =COUNTIFS($G$2:$G$6,">="&18,$G$2:$G$6,"<="&28)

In cell B14: =COUNTIFS($G$2:$G$6,">="&29,$G$2:$G$6,"<="&38)

In cell B15: =COUNTIFS($G$2:$G$6,">="&39,$G$2:$G$6,"<="&48)

In cell B16: =COUNTIFS($G$2:$G$6,">="&49,$G$2:$G$6,"<="&58)

In cell B17: =COUNTIF($G$2:$G$6,">"&58)Your sheet should now look like:

A B C D E F G 1) ID LastName FirstName DateOfBirth Sex Charges Age 2) NH1389 Doe John 12/5/1974 Male Domestic Battery 43 3) JU345 Smith Terry 2/21/1986 Male DUI 32 4) TY6745 Johnson Tim 3/1/1969 Male Domestic Battery 49 5) IU987 Doe Jane 7/1/1978 Female Domestic Battery 40 6) WE345 Greer Nancy 4/15/1978 Female Retail Theft 40 7) 8) 9) DB Male 2 10) DB Female 1 11) 12) less than 18 0 13) between 18 and 28 0 14) between 29 and 38 1 15) between 39 and 48 3 16) between 49 and 58 1 17) older than 58 0

See how that works for you.

Any question, please ask.MIKE

message edited by mmcconaghy

First, I apologize for posting in the wrong section. I will watch that in the future. I had thought of the solution you've suggested above, it is a simple solution and will diffinately work. This excel database holds all the data from an app that I developed. I had hoped to avoid creating additional fields like you are suggested above and keep everything in the fields established and just use a formula to get the desired information.

Thank you so much for your help, this is a good solution.

I had hoped to avoid creating additional fields

You could always hide the Age column if you do not wish to have it displayed.

just use a formula to get the desired information.OK, here are SUMPRODUCT() formulas that should get you what you want:

With your sheet like:

A B 11) 12) less than 18 13) between 18 and 28 14) between 29 and 38 15) between 39 and 48 16) between 49 and 58 17) older than 58To get your count:

In cell B12 enter the formula: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)<18))

In cell B13: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)>=18),--(YEAR(TODAY())-YEAR($D$2:$D$6)<=28))

In cell B14: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)>=29),--(YEAR(TODAY())-YEAR($D$2:$D$6)<=38))

In cell B15: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)>=39),--(YEAR(TODAY())-YEAR($D$2:$D$6)<=48))

In cell B16: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)>=49),--(YEAR(TODAY())-YEAR($D$2:$D$6)<=58))

In cell B17: =SUMPRODUCT(--(YEAR(TODAY())-YEAR($D$2:$D$6)>58))

See how that works for you.

MIKE

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History