Count birthdays by year

In this example, the goal is to count birthdays by year. The source data is an Excel Table named data in the range C5:C16. The birthdays we want to count are in the Birthday column. In column E, the years of interest have been previously entered. 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 easiest way to solve this problem is to use the SUMPRODUCT function together with the YEAR function like this in cell F5:

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

Working from the inside out, we use the YEAR function to extract the year from each birthday:

YEAR(data[Birthday]) // 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 a new 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. We want to count the TRUE values, but because SUMPRODUCT will ignore the logical values TRUE and FALSE, we need to convert these values to 1s and 0s first. To perform this conversion, we use a double negative (--). The result is an array that contains just 1s and 0s, which is returned directly to the SUMPRODUCT function like this:

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

With only one array to process, SUMPRODUCT sums the array and returns a result of 3 in cell F5. As the formula is copied down, it returns a count of birthdays per year as seen in the worksheet.

Note: The SUMPRODUCT formula above is an example of using Boolean logic in an array operation. This is a powerful and flexible approach to solving many problems in Excel. It is also an important skill with new functions like FILTER and XLOOKUP, which often use this technique to apply multiple criteria (FILTER example, XLOOKUP example)

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 COUNTIFS. Instead, you must create a start and end date for each year. The formula looks like this:

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

In a nutshell, we create a first-of-year date (1-Jan-1999) and an end-of-year date (31-Jan-1999) using the year in E5 with the DATE function:

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

The DATE function creates Excel dates with separate year, month, and day arguments. In the example, month and day are hard-coded, and we get year from column E. These dates are concatenated to the greater than or equals to operator (>=) to make criteria1, and the less than or equals to operator (<=) to make criteria2. The range for both criteria is data[Birthday]. Notice the operators must be enclosed in double quotes ("").

As the formula is copied down column F, it returns a count of birthdays per year, same as the SUMPRODUCT formula.

Dynamic array solution

In the latest version of Excel, which supports dynamic array formulas, it is possible to create a single all-in-one formula that builds the entire summary table, including headers, like this:

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

The LET function is used to assign three intermediate variables: years, uyears, and counts. The value for years is created like this:

YEAR(data[Birthday]) // extract years

Here, the YEAR function is used to extract just the year from all dates in data[Birthday]. Because the table contains 12 rows, the result is an array with 12 year values like this:

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

Next, the value for uyears (unique years) is created like this:

SORT(UNIQUE(years)) // get and sort unique years

Out of 12 year values, the UNIQUE function returns just 4 unique years:

{1999;2000;2001;2002} // unique

This array is returned to the SORT function, which returns an array sorted in ascending order:

={1999;2000;2001;2002} // sorted

In this example, it happens that the unique years are already in ascending order, so the SORT function does not change the result from UNIQUE. However, using the SORT function ensures that year values will always appear in order when source data is not sorted.

Next, the BYROW function is used to create a value for counts for each year like this:

BYROW(uyears, LAMBDA(r, SUM(--(years=r)))) // counts

BYROW runs through the uyears values row by row. At each row, it applies this calculation:

LAMBDA(r, SUM(--(years=r)))

The value for r is the year in the "current" row. Inside the SUM function, this value is compared to years. Since years contains all 12 years, the result is an array with 12 TRUE and FALSE results. The TRUE and FALSE values are converted to 1s and 0s with the double negative (--), and the SUM function simply adds up the result, which is the count of birthdays associated with the current row. Since there are 4 unique years, the result from BYROW is an array with 4 counts like this:

={3;5;3;1} // counts

Finally the HSTACK and VSTACK functions are used to assemble a complete table:

VSTACK({"Year","Count"},HSTACK(uyears, counts))

At the top of the table, the array constant {"Year","Count"} creates a header row. The HSTACK function combines uyears and counts horizontally, and VSTACK combines the header row and the data to make the final table. The result spills into multiple cells on the worksheet:

Pivot table solution

A Pivot Table is a good solution for this problem as well. This example shows how to count birthdays by month with a Pivot Table.