Count birthdays by month

You would think you could use the COUNTIF function to count birthdays, but the trouble is COUNTIF only works with ranges, and won't let you use something like MONTH to extract just the month number from dates. So, we use the SUMPRODUCT function with custom logic instead.

Inside SUMPRODUCT, we have this expression:

MONTH(birthdays)=MONTH(D5&1)

On the right, the MONTH function extracts the month for each date in the named range "birthdays". On the right, the MONTH function is used to get a number for each month name shown in the table. This is a standard number between 1-12, and the details of this formula are explained here.

The results from these two expressions are then compared. Because we are working with 100 birthdays, the result is an array of 100 TRUE / FALSE values, where each TRUE represents a date where month numbers from the birthdays are the same as the month number from the name in column D. Next, the TRUE FALSE values are then converted to ones and zeros with the double negative (--).

In cell E5, the resulting array looks like this inside SUMPRODUCT:

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

SUMPRODUCT then sums these numbers and returns a final result, in this case, 9. As the formula is copied down, it returns the total count of birthdays in each month as listed.

Dealing with empty cells

If you have blank cells in the list of birthdays, you will get incorrect results, since MONTH (0) returns 1. To handle blank cells, you can adjust the formula as follows:

=SUMPRODUCT((MONTH(birthdays)=MONTH(D5&1))*(birthdays<>""))

Multiplying by the expression (birthdays<>"") effectively cancels out values for empty cells. See the SUMPRODUCT page for more information about how logical expressions work inside SUMPRODUCT.

Pivot table solution

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