Count birthdays by year in Excel

If you have a list of dates of birth and want to know how many birthdays fall in each year, Excel can do the heavy lifting for you. In real life, this is super handy for HR reports, school batches, or customer age analysis.

Below is a practical, beginner‑friendly guide that shows three approaches: a classic SUMPRODUCT formula, a COUNTIFS date‑range method, and a modern dynamic array “all‑in‑one” summary.

What “count birthdays by year” really means

At a basic level, “count birthdays by year” means:

You have a column of full dates (like 15‑03‑1999, 22‑07‑2000, etc.).
You want to know how many of those dates belong to each calendar year (1999, 2000, 2001…).

So instead of manually filtering for each year, you let Excel:

Extract the year from each date.
Compare those years against a list of target years.
Count how many dates match each year.

This might sound a bit confusing at first, but once you see the formulas, it becomes much clearer.

Why counting birthdays by year is useful

You’ll probbably see this kind of requirement in many day‑to‑day reports, not just for birthdays.

Some common scenarios:

HR / Employee data: Count employees hired or born in each year to see hiring trends or age bands.
Education: Count students’ birth years to understand which cohorts are growing.
Customer analytics: Group customers by birth year for marketing segmentation.

The nice part is that the same formulas work for any dates, not only birthdays: order dates, join dates, transaction dates, etc.

Method 1: SUMPRODUCT + YEAR (simple and powerful

This is usually the easiest and most flexible way to count birthdays per year in a normal Excel sheet.

Sample setup

Imagine this data:

Table data with a column Birthday (C5:C16).
In column E, you type the years you care about: 1999, 2000, 2001, 2002.
In column F, next to each year, you want the count.

In cell F5, you can use:

text=SUMPRODUCT(--(YEAR(data[Birthday]) = E5))

This formula:

Uses YEAR(data[Birthday]) to create an internal list of years from all birthdays.
Compares each of those to the year in E5 (for example 1999), generating TRUE/FALSE values.
Converts TRUE/FALSE into 1/0 with the -- (double negative).
Lets SUMPRODUCT add those 1s and return the total count for that year.

Copy it down, and you’ll see the count for each year in your list.

Why SUMPRODUCT is nice here

It works directly with the YEAR function, no extra helper columns needed.
It easily handles table references (like data[Birthday]) and dynamic ranges as your data grows.
It is very flexible if later you want to add more conditions (e.g., “only active employees”).

Method 2: COUNTIFS with date range (classic and compatible)

COUNTIFS is another solid choice, especially if you like working with date ranges instead of extracting the year.

The main catch is that COUNTIFS wants actual cell ranges as criteria ranges; you can’t just feed YEAR(dates) directly inside it. So the trick is: for each target year, build a start date (1‑Jan of that year) and an end date (31‑Dec of that year).

Example formula

Using the same setup:

data[Birthday] holds your birthdays.
E5 holds a year like 1999.

In F5, you can use:

text=COUNTIFS(
  data[Birthday], ">=" & DATE(E5, 1, 1),
  data[Birthday], "<=" & DATE(E5, 12, 31)
)

What this does:

DATE(E5,1,1) builds the first day of that year (01‑Jan‑1999 if E5 is 1999).
DATE(E5,12,31) builds the last day of that year.
COUNTIFS counts how many birthdays fall between these two dates (inclusive).

Again, copy it down for the other years in column E, and you’ll get matching counts.

When to prefer COUNTIFS

You need explicit date boundaries (useful for audits or when explaining logic to non‑Excel folks).
You’re already using COUNTIFS for other criteria and want a consistent pattern.

It’s a bit more verbose than the SUMPRODUCT version, but still very readable.

Method 3: Dynamic array “all‑in‑one” summary (modern Excel)

If you’re on a newer Excel version that supports dynamic arrays (Microsoft 365 or Excel 2021+), you can build a single formula that spills a complete “Year / Count” summary table.

In real life, this is amazing for quick dashboards or ad‑hoc reports where you don’t want to manually maintain year lists.

The dynamic array formula

Assuming your data is still in data[Birthday], you can enter this in one cell (say H5):

text=LET(
  years, YEAR(data[Birthday]),
  uyears, SORT(UNIQUE(years)),
  counts, BYROW(uyears, LAMBDA(r, SUM(--(years = r)))),
  VSTACK({"Year","Count"}, HSTACK(uyears, counts))
)

What each part does:

years: extracts the year from every birthday.
uyears: gets the unique years and sorts them.
counts: for each unique year, uses BYROW + LAMBDA to sum how many times that year appears in years.
HSTACK joins years and counts side‑by‑side; VSTACK adds a header row on top.

The result is a neat two‑column table (Year / Count) that spills automatically.

Why dynamic arrays feel so good

You don’t have to maintain a manual list of years somewhere else.
When new birthdays are added to the underlying table, the summary updates and spills further down as needed.
The formula is centralized in one place, which reduces errors in complex workbooks.

Practical example: HR birthday report

Let’s walk through a small, realistic HR scenario.

Say you have a Staff table with these columns: Name, Department, Birthday. You want a quick report of how many employees were born in each year, just to see the distribution of age groups.

Convert your range to a proper Excel Table and name it Staff.
Make sure Birthday is a date column, not text.
Add a new sheet called “Birthday Summary”.
If you’re on modern Excel, put the dynamic array formula (adapted) in A2:text=LET( years, YEAR(Staff[Birthday]), uyears, SORT(UNIQUE(years)), counts, BYROW(uyears, LAMBDA(r, SUM(--(years = r)))), VSTACK({"Year","Count"}, HSTACK(uyears, counts)) )
Format the spilled table as a proper report (bold header, maybe a simple column chart).

Now every time you add or remove employees from Staff, your birthday summary updates automatically. In a real HR dashboard, you’d probbably combine this with department filters or slicers, but the core logic is the same.

Tips, best practices and common mistakes

A few small details can save you lots of debugging time.

Get date formats right

Always ensure cells are true dates, not text that just look like dates.
You can test by changing the cell format to a number; if it becomes a large serial number, Excel stores it as a date.

Use Excel Tables where possible

Turning your range into a Table (Ctrl+T) makes formulas like data[Birthday] automatically expand as you add rows.
This reduces the chance of “forgot to update the range” errors, especially in monthly reports.

Watch out for blank dates and errors

Blank cells in the birthday column will cause YEAR to return an error, which can break your formula.
Consider cleaning data first or wrapping YEAR in something like IFERROR in more advanced setups.

Choose the right method for your Excel version

If you’re on older Excel, stick to SUMPRODUCT or COUNTIFS—they work everywhere.
If you have dynamic arrays, take advantage of LET, UNIQUE, SORT, BYROW, VSTACK, and HSTACK for cleaner summary logic.

Key takeaways and what to try next

Counting birthdays by year in Excel is basically about reading the year part of each date and then counting how many times each year appears. You can do this with a compact SUMPRODUCT + YEAR formula, with a more explicit COUNTIFS + DATE range method, or with a single dynamic array formula that spills a full summary table.

If you already have a birthday or customer DOB list, try all three methods once on a copy of your data. In real life, you’ll quickly see which style feels more natural to you and to your team. From there, you can extend the idea to count by month, quarter, or even build an age‑band report using similar logic.

For your next step, take one real dataset—maybe from your HR system or CRM—and build a small “Birthdays by Year” summary using the dynamic array version. Once that feels comfortable, experiment by adding filters or charts so that this simple year count becomes part of a more complete reporting dashboard.