# Solved How to COUNTIF for Age range between two dates

April 13, 2018 at 07:45:20
Specs: Windows 7
 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/1994 CourtTbl: 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 DUI I am having trouble getting a COUNTIF to work correctly. Thank you in advance for your assistance. message edited by Haus

See More: How to COUNTIF for Age range between two dates

April 15, 2018 at 09:23:52
 I had hoped to avoid creating additional fieldsYou 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 58 To 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.MIKEhttp://www.skeptic.com/

#1
April 13, 2018 at 10:31:04
 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.MIKEmessage edited by mmcconaghy

Report •

#2
April 13, 2018 at 15:40:10
 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 58 First get their age: In cell G2 enter the formula: =(TODAY()-D2)/365.25This will give you the age in Years onlyDrag 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.MIKEmessage edited by mmcconaghy

Report •

#3
April 14, 2018 at 10:58:01
 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.

Report •

Related Solutions

#4
April 15, 2018 at 09:23:52