Count Between Dates by Age Range in Excel

Count Between Dates by Age Range in Excel

One of Excel’s most common uses in data analytics, HR, school reports, or customer tracking is filtering and counting records that fall between two specific dates — like counting how many people joined in a month. But sometimes, you also want to know how many of those people fall into particular age groups such as “0–20,” “21–30,” and so on.

Doing this manually takes forever, especially when your data keeps changing. Thankfully, Excel can handle this easily with a few clever formulas.

This article explains how to count entries between two dates and by age range using the COUNTIFS, SUMPRODUCT, and new TEXTBEFORE/TEXTAFTER functions.

Why Use Date and Age Ranges Together

Counting between dates is simple, but when you also need to factor in an age group, the challenge comes from the fact that Excel often stores those ranges like “0-20” as plain text. That means the software doesn’t automatically know that this means “greater than or equal to 0” and “less than or equal to 20.”

So, our goal is to teach Excel how to interpret that text correctly — by splitting the range into two numbers (a low and a high boundary) before using it in the formula.

Setup Example Data

Let’s imagine a dataset like this in Excel:

Name	Age	Joined
Asha	19	1-Jan-2024
Ravi	25	15-Jan-2024
Meera	33	24-Jan-2024
Deep	42	4-Feb-2024
Rahul	55	10-Feb-2024

And for our parameters:

• Start date: 1-Jan-2024 (named range start)
• End date: 31-Jan-2024 (named range end)

You’ve also created a list of age groups in column G:

Age Group (G)
0-20
21-30
31-40
41-60

We now want to count how many people joined between these two dates while also belonging to each age range.

Step 1: Count Between Two Dates Using COUNTIFS

To count everyone who joined between the start and end dates, use this simple formula:

text=COUNTIFS(joined, ">=" & start, joined, "<=" & end)

This checks whether the “joined” date is greater than or equal to the start date and less than or equal to the end date. Every record meeting both conditions is counted.

So in our dataset above, Asha, Ravi, and Meera all fall between Jan 1 and Jan 31 — the formula returns 3.

Step 2: Add Age Range Logic

The next step is to limit the count based on the age range listed in column G. The tricky part is splitting “21-30” into 21 (low) and 30 (high).

To extract the lower bound, use:

text=LEFT(G8, FIND("-", G8) - 1)

To extract the upper bound, use:

text=RIGHT(G8, LEN(G8) - FIND("-", G8))

Once you have both, you can add them to your COUNTIFS formula:

text=COUNTIFS(joined, ">=" & start,
           joined, "<=" & end,
           age, ">=" & LEFT(G8, FIND("-", G8) - 1),
           age, "<=" & RIGHT(G8, LEN(G8) - FIND("-", G8)))

That’s it — the formula now counts records that meet both date and age range criteria.

For example, for the “0-20” row, Excel returns 1 (Asha), since she’s the only one under 20 who joined in January.

Step 3: Understanding the Formula Logic

When G8 = “0-20”,

• LEFT(G8, FIND("-", G8) - 1) returns “0”
• RIGHT(G8, LEN(G8) - FIND("-", G8)) returns “20”

So the whole formula becomes:

text=COUNTIFS(joined,">="&start,joined,"<="&end,age,">=0",age,"<=20")

When copied down, Excel automatically adjusts the G8 reference to G9, G10, etc., meaning the age filters update for each group.

Step 4: Using SUMPRODUCT as an Alternative

If you prefer working with array logic, try SUMPRODUCT instead. It’s a powerful alternative that gives precise control.

Count only by dates:

text=SUMPRODUCT((joined >= start) * (joined <= end))

Count by both date and age range:

text=SUMPRODUCT((joined >= start) * (joined <= end) *
(age >= LEFT(G8, FIND("-", G8) - 1) + 0) * 
(age <= RIGHT(G8, LEN(G8) - FIND("-", G8)) + 0))

Notice the +0 parts — these convert the extracted text values into numbers. Without this conversion, Excel might not recognize them in numeric comparisons.

Step 5: Making It Cleaner with TEXTBEFORE and TEXTAFTER

If you’re using Excel 365, good news — Microsoft has made this process simpler. With the new TEXTBEFORE and TEXTAFTER functions, you don’t need to combine LEFT, RIGHT, or FIND anymore.

Here’s how the shorter COUNTIFS version looks:

text=COUNTIFS(joined, ">=" & start,
           joined, "<=" & end,
           age, ">=" & TEXTBEFORE(G8, "-"),
           age, "<=" & TEXTAFTER(G8, "-"))

And here’s the SUMPRODUCT version:

text=SUMPRODUCT((joined >= start) * (joined <= end) *
(age >= TEXTBEFORE(G8, "-") + 0) *
(age <= TEXTAFTER(G8, "-") + 0))

The new functions handle the text parsing automatically, making your formula cleaner and easier to understand.

Step 6: Practical Use Cases

Once you master this, you’ll find it useful in many real-world tasks:

• HR Reports – Counting employees who joined in a specific period by age group.
• Marketing Analytics – Tracking customers by signup date and demographic.
• Education Data – Filtering student enrollments by date and age category.
• Event Registration – Categorizing attendees by date of registration and age bracket.

With a single formula, you can automate complex counting logic without writing a single line of VBA.

Step 7: Pro Tips

• Use named ranges (joined, start, end, age) to keep your formulas readable.
• Always verify your date formats — Excel sometimes interprets text dates differently based on your region settings.
• When sharing files, check formula compatibility if the recipient isn’t using Excel 365 (TEXTBEFORE/TEXTAFTER won’t work in older versions).

Final Thoughts

Counting between two dates by age range might seem complicated, but once you understand how to parse the text-based ages, it’s purely logical. COUNTIFS handles it effectively, and with Excel 365, TEXTBEFORE and TEXTAFTER make it nearly effortless.

Even small reporting tasks become fully automated and dynamic. Whether you’re building dashboards for a manager or analyzing data for a school project, this method saves time and cuts down on repetitive manual calculations.