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

Reply ↓  Report •

✔ Best Answer
April 15, 2018 at 09:23:52
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 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.

MIKE

http://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.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  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.25
This will give you the age in Years only
Drag down five rows

Next 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

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  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.


Reply ↓  Report •

Related Solutions

#4
April 15, 2018 at 09:23:52
✔ 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 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.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Ask Question