Counting entries between dates while categorizing them by age range is a common task in data analysis. This tutorial will provide you through creating an Excel formula that utilizes COUNTIFS, FIND, LEFT, RIGHT, SUMPRODUCT, and TEXTBEFORE functions to achieve this.
Example Scenario
Suppose you have a dataset of employees with their birth dates and registration dates, and you need to count how many employees fall within a specific age range for a given time period.
- Column A: Employee Name
- Column B: Date of Birth
- Column C: Registration Date
- Column D: Age (calculated)
Example Data:
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Date of Birth | Registration Date |
| 2 | John | 03/12/1985 | 15/06/2022 |
| 3 | Mary | 10/08/1990 | 20/07/2022 |
| 4 | Alex | 25/03/2000 | 05/01/2023 |
| 5 | David | 12/09/1995 | 14/03/2023 |
You need to count employees aged 20-30 years old who registered between January 1, 2022, and December 31, 2023.
Step 1: Calculate Age
To calculate an employee’s age based on their birth date and a reference date, use:
=DATEDIF(B2, TODAY(), "Y")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Date of Birth | Registration Date | Age |
| 2 | John | 03/12/1985 | 15/06/2022 | =DATEDIF(B2, TODAY(), "Y") |
| 3 | Mary | 10/08/1990 | 20/07/2022 | =DATEDIF(B3, TODAY(), "Y") |
| 4 | Alex | 25/03/2000 | 05/01/2023 | =DATEDIF(B4, TODAY(), "Y") |
| 5 | David | 12/09/1995 | 14/03/2023 | =DATEDIF(B5, TODAY(), "Y") |
The output be like:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Date of Birth | Registration Date | Age |
| 2 | John | 03/12/1985 | 15/06/2022 | 38 |
| 3 | Mary | 10/08/1990 | 20/07/2022 | 33 |
| 4 | Alex | 25/03/2000 | 05/01/2023 | 23 |
| 5 | David | 12/09/1995 | 14/03/2023 | 28 |
This calculates the age based on today's date. If you want a specific reference date instead of today’s date, replace TODAY() with your preferred date.
Now, extend this formula down to calculate the age of all employees in the dataset.
Step 2: Count Employees Based on Age Range and Registration Date
Using COUNTIFS
The COUNTIFS function is best for counting entries based on multiple criteria.
Formula to count employees aged between 20 and 30 who registered between January 1, 2022, and December 31, 2023:
=COUNTIFS(D2:D100,">=20",D2:D100,"<=30",C2:C100,">=01/01/2022",C2:C100,"<=31/12/2023")
Explanation:
-
D2:D100, ">=20"→ Counts employees with age 20 or above -
D2:D100, "<=30"→ Counts employees 30 or below -
C2:C100, ">=01/01/2022"→ Ensures registration date is on or after Jan 1, 2022 -
C2:C100, "<=31/12/2023"→ Ensures registration date is on or before Dec 31, 2023
Step 3: Using SUMPRODUCT for Advanced Filtering
If you prefer a more flexible approach, SUMPRODUCT can also be used:
=SUMPRODUCT((D2:D100>=20)*(D2:D100<=30)*(C2:C100>=DATE(2022,1,1))*(C2:C100<=DATE(2023,12,31)))
This formula works similarly but allows for more customization.
Step 4: Extracting Age from Text Data Using FIND, LEFT, RIGHT, and TEXTBEFORE
If your dataset stores ages as part of a text entry (e.g., "John - Age 25" in a single cell), you can extract the age using FIND, LEFT, and TEXTBEFORE:
=TEXTBEFORE(RIGHT(A2,LEN(A2)-FIND("-",A2)), " ")
This extracts the age number from a text format like "John - Age 25".
You can then use this extracted age in your COUNTIFS formula.
Conclusion
Using these Excel formulas, you can efficiently count employees who fall within a specific age range and have registered within a specific date range. The combination of COUNTIFS, SUMPRODUCT, FIND, LEFT, RIGHT, and TEXTBEFORE allows for flexibility in handling structured and unstructured data. Find more interesting tutorials at excel24x7.com
Top comments (0)