Blog

Introduction

Count between dates by age range is counting records that fall between two dates and belong to a particular age range is a common requirement in reporting, cohort analysis, HR dashboards, and epidemiology. In this step-by-step guide you’ll learn how to use Excel formulas — COUNTIFS, SUMPRODUCT, FIND, LEFT, RIGHT, and TEXTBEFORE — separately and in combination to count rows between dates by age range. This article is written for beginners with clear examples, a practice dataset, screenshots, and a downloadable Excel workbook.

1. Example dataset (Practice table)

Header (all examples are in headers):

Example Data — Date, Name, DOB, Age, AgeRange

A compact sample (first 8 rows) used in screenshots and the downloadable workbook:

Date	Name	DOB	Age	AgeRange
2025-01-05	Alice Khan	1998-04-10	27	25-34
2025-02-10	Bob Singh	1987-09-20	37	35-44
2025-02-15	Cara Rao	2001-12-01	23	18-24
2025-03-03	Dan Patel	1994-06-30	30	25-34
2025-03-10	Eve Thomas	1980-01-25	45	45-54
2025-03-12	Frank Roy	1990-11-11	34	25-34
2025-04-01	Gina Verma	1978-02-05	47	45-54
2025-04-10	Hari Mehta	2005-08-08	19	18-24

Place this dataset in a sheet named Data starting at A1.

2. Goal: Count between dates by age range

In count between dates by age range, we want answers like: How many records between 2025-02-01 and 2025-03-31 are in the 25-34 age range?

General approach:

• Ensure dates are real Excel dates.
• Have an Age column (numeric) or AgeRange column (text like 25-34).
• Use COUNTIFS when columns are already numeric/text-ready.
• Use SUMPRODUCT or helper formulas when you need to extract numbers from text age ranges.

3. Method A — Simple: COUNTIFS with explicit Age column

Header Example: COUNTIFS — Count rows between two dates where Age is between 25 and 34

If your sheet Data has Date in A, Age in D (numeric), use:

=COUNTIFS(Data!A:A, ">=" & DATE(2025,2,1), Data!A:A, "<=" & DATE(2025,3,31), Data!D:D, ">=" & 25, Data!D:D, "<=" & 34)

Explanation:

• COUNTIFS accepts paired ranges and conditions.
• The first two pairs restrict Date between 2025-02-01 and 2025-03-31.
• The next two pairs restrict Age between 25 and 34.

Tip for beginners: Use "& DATE(yyyy,mm,dd) or cell references holding start and end dates: "& $G$1.

4. Method B — COUNTIFS when Age stored as AgeRange text (like 25-34)

Header Example: COUNTIFS + exact match on AgeRange

If AgeRange column is in E (text), and you want exactly 25-34 between dates for count between dates by age range

=COUNTIFS(Data!A:A, ">=" & $G$1, Data!A:A, "<=" & $G$2, Data!E:E, "25-34")

Where $G$1 = start date and $G$2 = end date.

Explanation: COUNTIFS tests Date range and exact AgeRange string.

5. Method C — SUMPRODUCT for flexible criteria and partial matches

Header Example: SUMPRODUCT — Count by age extracted from AgeRange text

If AgeRange values vary ("25-34", "35-44"), and you want to count rows where the lower bound is 25 and the upper bound is 34, or where Age falls inside numeric bounds that must be extracted from text, use SUMPRODUCT:

=SUMPRODUCT((Data!A2:A100>= $G$1)*(Data!A2:A100<= $G$2)*((--LEFT(Data!E2:E100, FIND("-", Data!E2:E100)-1) >= 25) * (--RIGHT(Data!E2:E100, LEN(Data!E2:E100) - FIND("-", Data!E2:E100)) <= 34) ))

Breakdown:

• (Data!A2:A100>= $G$1) and (<= $G$2) are logical arrays for date filtering.
• FIND("-", Data!E2:E100) locates the dash inside AgeRange.
• LEFT(..., FIND(...) - 1) extracts the lower bound as text; prefix -- converts it to number.
• RIGHT(..., LEN(...) - FIND(...)) extracts the upper bound.
• SUMPRODUCT multiplies boolean arrays and sums the results.

Note: This formula assumes every cell in E2:E100 contains a dash -.

6. Extracting numbers from text: FIND, LEFT, RIGHT, TEXTBEFORE

Sometimes age range text is inconsistent and TEXTBEFORE (Excel 365/2021) makes extraction cleaner.

Header Example: TEXTBEFORE + TEXTAFTER (or RIGHT) — Extract lower bound

=--TEXTBEFORE(E2, "-")   // gives lower bound as number when coerced with --

Extract upper bound (Text after dash):

=--TEXTBEFORE(TEXTAFTER(E2, "-"), " ")   // or --TEXTAFTER(E2, "-") if TEXTAFTER is available

If TEXTBEFORE/TEXTAFTER are unavailable, use FIND, LEFT, RIGHT, MID, LEN as shown earlier.

7. Putting it together: Example workbook steps for beginners

1. Create sheet Data and paste the Example Data table starting at A1.
2. Create a small Parameters area: G1 = StartDate (2025-02-01), G2 = EndDate (2025-03-31), G4 = AgeRangeWanted (25-34).
3. Try the COUNTIFS exact range formula (Method B) in a cell to get quick result.
4. Try the COUNTIFS numeric Age formula (Method A) if you have Age numeric.
5. Use the SUMPRODUCT + LEFT/RIGHT formula (Method C) to practice extracting numbers when AgeRange is text.
6. If you have Excel 365, replace LEFT/RIGHT with --TEXTBEFORE(E2, "-") and --TEXTAFTER(E2, "-") for cleaner formulas.

8. Table list (quick reference)

• Data sheet: Columns A:E — Date, Name, DOB, Age, AgeRange.
• Parameters area: G1 StartDate, G2 EndDate, G4 AgeRangeWanted.
• Example formulas: COUNTIFS (numeric), COUNTIFS (AgeRange text), SUMPRODUCT (extraction).

Check next blog: https://learnproexcel.com/how-to-combine-text-in-excel/