To count values between two dates that also fall into specific numeric ranges, such as age groups, you can use a formula based on the COUNTIFS function, with help from the LEFT, RIGHT, FIND, and LEN functions. The COUNTIFS function allows you to count cells that meet multiple criteria, such as date and age ranges. The LEFT, RIGHT, FIND, and LEN functions help you to extract the low and high numbers from the age range labels, which are text values.
Procedures
- Prepare your data in columns, such as name, birth date, join date, etc. Make sure the dates are formatted as dates in Excel.
- Calculate the age of each individual as of a particular date, such as the current date or the end date. You can use the DATEDIF function or the YEARFRAC function to do this. For example, if the birth date is in column B and the end date is in cell E1, you can use this formula to calculate the age in years:
=DATEDIF(B2,E$1,"y")
or this formula to calculate the age in years with decimals:
=YEARFRAC(B2,E$1,1)
- Create a table with the age range labels in one column and the corresponding count formulas in another column. For example, if the age range label is “25-30” and the age is in column C, you can use this formula to count the values that fall into this range:
=COUNTIFS(C:C,">="&LEFT(G2,FIND("-",G2)-1),C:C,"<="&RIGHT(G2,LEN(G2)-FIND("-",G2)))
where G2 is the cell with the age range label.
- Copy the formula down for the other age ranges. You can also add a total row to sum up the counts.
Explanation
The formula for counting between dates by age range consists of four parts:
- The COUNTIFS function, which counts cells that meet multiple criteria. It takes the form of:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
where criteria_range is the range of cells to evaluate, and criteria is the condition to be met.
- The joined and start/end criteria, which restrict the count to the dates that fall between the start and end dates. For example, if the join date is in column D and the start and end dates are in cells E2 and E3, you can use these criteria:
D:D,">="&E$2 // greater than or equal to start date D:D,"<="&E$3 // less than or equal to end date
Note that we use the ampersand (&) to concatenate the operators with the cell references, and we use absolute references ($) to fix the row numbers of the start and end dates.
- The age and LEFT/RIGHT criteria, which restrict the count to the ages that fall into the age range. For example, if the age is in column C and the age range label is “25-30” in cell G2, you can use these criteria:
C:C,">="&LEFT(G2,FIND("-",G2)-1) // greater than or equal to low number C:C,"<="&RIGHT(G2,LEN(G2)-FIND("-",G2)) // less than or equal to high number
Here, we use the LEFT, RIGHT, FIND, and LEN functions to extract the low and high numbers from the age range label, which is a text value. The FIND function returns the position of the hyphen (-) in the label, and the LEN function returns the length of the label. The LEFT function returns the leftmost characters of the label, up to the position of the hyphen minus one. The RIGHT function returns the rightmost characters of the label, starting from the position of the hyphen plus one.
- The comma (,), which separates the range/criteria pairs in the COUNTIFS function. You can add as many pairs as you need, as long as they are separated by commas.
Scenario
Suppose you have a list of employees with their names, birth dates, and join dates, as shown below. You want to count how many employees joined the company between January 1, 2020 and December 31, 2020, and also fall into the age ranges of 20-25, 26-30, 31-35, 36-40, and 41+. You also want to display the results in a table and a chart.
To do this, you can follow these steps:
- Calculate the age of each employee as of December 31, 2020, using the DATEDIF function. For example, in cell E2, enter this formula and copy it down:
=DATEDIF(B2,$H$2,"y")
where B2 is the birth date and H2 is the end date.
- Create a table with the age range labels in column G and the count formulas in column H. For example, in cell H3, enter this formula and copy it down:
=COUNTIFS(D:D,">="&$H$1,D:D,"<="&$H$2,E:E,">="&LEFT(G3,FIND("-",G3)-1),E:E,"<="&RIGHT(G3,LEN(G3)-FIND("-",G3)))
where D:D is the join date, H1 and H2 are the start and end dates, E:E is the age, and G3 is the age range label.
- For the last age range of 41+, use this formula instead:
=COUNTIFS(D:D,">="&$H$1,D:D,"<="&$H$2,E:E,">="&LEFT(G7,FIND("+",G7)-1))
where G7 is the age range label of “41+”.
- Add a total row to sum up the counts. For example, in cell H8, enter this formula:
=SUM(H3:H7)
- Create a chart from the table data. Select the range G2:H7, and insert a column chart. You can format the chart as you like.
Other approaches
Besides using the COUNTIFS function, you can also use other approaches to count between dates by age range in Excel, such as:
- Using the SUMPRODUCT function, which multiplies arrays and returns the sum of the products. For example, you can use this formula to count the values between two dates and an age range:
=SUMPRODUCT((D:D>=$H$1)*(D:D<=$H$2)*(E:E>=LEFT(G3,FIND("-",G3)-1))*(E:E<=RIGHT(G3,LEN(G3)-FIND("-",G3))))
where D:D is the join date, H1 and H2 are the start and end dates, E:E is the age, and G3 is the age range label.
- Using the FREQUENCY function, which returns a frequency distribution of values in an array. For example, you can use this formula to count the values between two dates and a list of upper bounds for the age ranges:
=FREQUENCY(E2:E16,J2:J6)
where E2:E16 is the age, and J2:J6 is the list of upper bounds for the age ranges, such as 25, 30, 35, 40, and 100. Note that you need to enter this formula as an array formula by pressing Ctrl + Shift + Enter.
- Using a pivot table, which summarizes data in a table or range. For example, you can create a pivot table from the data, and add the join date and age fields to the rows area, and the count of name field to the values area. Then, you can group the join date and age fields by the start and end dates and the age ranges, respectively.
