Exceljet

Quick, clean, and to the point

Count birthdays by year

Excel formula: Count birthdays by year
Generic formula 
=SUMPRODUCT(--(YEAR(dates)=year))
Summary 

To count the number of birthdays in a list by year, you can use a formula based on the SUMPRODUCT and YEAR functions. In the example shown, E5 contains this formula:

=SUMPRODUCT(--(YEAR(birthdays)=E5))

where birthdays is the named range (C5:B16), which contains 12 birthdays. As the formula is copied down, it returns the total count of birthdays in each year as shown.

Explanation 

In this example, the goal is to count birthdays by year. The source data is the table on the left, which contains first names in column B and birthdays in column C. For convenience, birthdays is the named range C5:C16. The easiest way to solve this problem is with the SUMPRODUCT function, but it can also be solved with COUNTIFS as explained below.

SUMPRODUCT function

The most straightforward way to solve this problem is to use the SUMPRODUCT function together with the YEAR function in a formula like this in cell F5:

=SUMPRODUCT(--(YEAR(birthdays)=E5))

Working from the inside out, we start by extracting the year from each birthday in the list:

YEAR(birthdays) // extract year

Because there are 12 dates in the list, the YEAR function returns 12 values in an array like this:

{1999;1999;2000;2000;2000;2000;2001;1999;2000;2001;2001;2002}

Next, each value in this array is compared against the year in E5, which is 1999. The result is another array containing only TRUE and FALSE values:

{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}

In this array, TRUE represents birth years equal to 1999 and FALSE represents birth years not equal to 1999.

Next, to convert the TRUE and FALSE values to 1s and 0s, we use a double negative (--). The result is an array that contains just 1s and 0s. This array is returned directly to the SUMPRODUCT function:

=SUMPRODUCT({1;1;0;0;0;0;0;1;0;0;0;0})

With just a single array to process, SUMPRODUCT sums up the elements in the array and returns a result of 3 in cell F5. As the formula is copied down, the same formula returns a count of birthdays for each year in column E.

COUNTIFS function

The COUNTIFS function can also be used to solve this problem but the formula is more complicated, because COUNTIF only works with ranges, and you can't extract the years to use as the range argument inside COUNTIF. The formula looks like this:

=COUNTIFS(birthdays,">="&DATE(E5,1,1),birthdays,"<="&DATE(E5,12,31))

In a nutshell, we create a start-of-year date (e.g. 1-Jan-1999) and an end-of-year date (e.g. 31-Jan-1999) for each year and use these dates for criteria inside COUNTIFS. To construct each date, we use the DATE function:

DATE(E5,1,1) // first day of year
DATE(E5,12,31) // last day of year

The DATE function makes it easy to build dates based on year, month, and day arguments that are either hard-coded or supplied as cell references. In the example, month and day are hard-coded, and we get year from column E.

The greater than or equal to operator (>=) and less than or equal to operator (<=) need to be enclosed in double quotes ("") and concatenated to each date because COUNTIFS is in a group of eight functions that require this special syntax.

Pivot table solution

A pivot table is an excellent solution for this problem as well.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.