How to Use Excel Formulas to Count Birthdays by Year

If you have a list of birthdays and you want to count how many of them fall in a certain year, you can use Excel formulas to do that. In this article, we will show you two ways to count birthdays by year in Excel: using the SUMPRODUCT function and using the COUNTIFS function.

Using the SUMPRODUCT Function

The SUMPRODUCT function can multiply corresponding elements in one or more arrays and return the sum of the products. We can use this function together with the YEAR function to count the number of birthdays by year. The YEAR function can extract the year from a date value.

The generic formula to count birthdays by year using the SUMPRODUCT function is:

=SUMPRODUCT(–(YEAR(dates)=year))

where dates is the range of cells that contain the birthdays, and year is the year of interest.

The double negative sign (–) is used to convert the logical values TRUE and FALSE to 1 and 0, respectively. This way, the SUMPRODUCT function can sum up the 1s and ignore the 0s.

Example

Suppose we have a list of birthdays in column A, and we want to count how many of them are in the year 2000. We can use the following formula in cell C2:

=SUMPRODUCT(–(YEAR(A2:A11)=2000))

This formula will return 4, as there are four birthdays in the year 2000.

We can copy the formula down to count the number of birthdays in other years, as shown in the table below:

Using the COUNTIFS Function

The COUNTIFS function can count the number of cells that meet one or more criteria. We can use this function to count the number of birthdays by year by specifying the criteria for the year.

The generic formula to count birthdays by year using the COUNTIFS function is:

=COUNTIFS(dates,“>=”&DATE(year,1,1),dates,“<=”&DATE(year,12,31))

where dates is the range of cells that contain the birthdays, and year is the year of interest.

The DATE function can create a date value from a given year, month, and day. We use the DATE function to create the start and end dates of the year, and use the comparison operators >= and <= to check if the birthdays are between those dates.

Example

Using the same data as before, we can use the following formula in cell D2 to count the number of birthdays in the year 2000:

=COUNTIFS(A2:A11,“>=”&DATE(2000,1,1),A2:A11,“<=”&DATE(2000,12,31))

This formula will also return 4, as there are four birthdays in the year 2000.

We can copy the formula down to count the number of birthdays in other years, as shown in the table below: