How to Count Birthdays by Year in Excel

Updated

Use a formula that tests the year of each birthday and counts the matches. Exceljet's featured example does this with SUMPRODUCT and YEAR:

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

This works when your birthdays are stored in an Excel Table named data, the birthday column is Birthday, and the year you want to count is in cell E5. The formula returns the number of birthdays that fall in that year.

How the formula works

The formula runs in three stages:

  1. YEAR(data[Birthday]) extracts the year from every date in the Birthday column, producing an array of years.
  2. =E5 compares each of those years to the target year in E5, producing an array of TRUE and FALSE values.
  3. The double negative -- converts TRUE to 1 and FALSE to 0, and SUMPRODUCT adds those 1s and 0s together.

The result is a single count. Because the reference is a structured table column, the formula expands automatically as you add rows.

Step-by-step setup

  1. Format your source data as an Excel Table (select the range, then Insert > Table) and give it a name such as data.
  2. Make sure the birthday column contains real dates, not text. YEAR only works on date values.
  3. In a separate cell, enter the year you want to count, for example 1990 in E5.
  4. Enter the formula:
=SUMPRODUCT(--(YEAR(data[Birthday])=E5))
  1. Copy the formula down next to a list of years to get a count for each one.

Verify the result

  • Check that the total of all year counts equals the number of rows in your table.
  • Spot-check a few birthdays manually against the source dates.
  • If a count looks wrong, confirm the birthday cells are dates and not text. A quick test is =ISNUMBER(data[Birthday]), which should return TRUE.

Common issues

  • Text dates. Dates imported as text won't be recognized by YEAR. Convert them with Date > Text to Columns or by re-entering them in a date format.
  • Wrong column name. The structured reference data[Birthday] must match the actual table and column names exactly.
  • Blank cells. Empty birthday cells are treated as year 1900 by YEAR, which can inflate counts for that year. Filter or exclude blanks if this matters.
  • Using a plain range instead of a table. You can replace data[Birthday] with a normal range like $C$5:$C$16, but you lose the automatic expansion when rows are added.

When to use this approach

This pattern is a good fit when you want a count per year without helper columns and your data lives in an Excel Table. If you need to count between two dates rather than by a single year, Exceljet also covers counting cells between dates, which uses a similar conditional-count structure.