Exceljet

Quick, clean, and to the point

Count birthdays by month

Excel formula: Count birthdays by month
Generic formula 
=SUMPRODUCT(--(MONTH(birthdays)=MONTH("name"&1)))
Summary 

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

=SUMPRODUCT(--(MONTH(birthdays)=MONTH(D5&1)))

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.

Explanation 

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.

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.