where birthdays is the named range (B5:B104), which contains 100 random birthdays. As the formula is copied down, it returns the total count of birthdays in each month as listed. Inside SUMPRODUCT, we have this expression: 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 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
