Count birthdays by month

Count birthdays by month

Generic formula 

=SUMPRODUCT(--(MONTH(birthday)=number))

Related formulas 

Count cells between two numbers

Summary count by month with COUNTIFS

Explanation

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

=SUMPRODUCT(--(MONTH(birthday)=D5))

This formula counts birthdays in January (since D5 contains 1) in the named range "birthdays" (B5:B104).

How this formula works

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 SUMPRODUCT instead.

Inside SUMPRODUCT, we have this expression:

MONTH(birthday)=D5)

The MONTH function extracts the month for each date in the named range "birthdays", and this is compared to the value in D5, which is 1. The result is an array of TRUE / FALSE values where each TRUE represents a date where month=1.

The TRUE FALSE values are then converted to ones and zeros with the double negative (--). SUMPRODUCT then sums these numbers and returns a final result.

Count birthdays by month

Generic formula 

=SUMPRODUCT(--(MONTH(birthday)=number))

Related formulas 

Count cells between two numbers

Summary count by month with COUNTIFS

Explanation

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

=SUMPRODUCT(--(MONTH(birthday)=D5))

This formula counts birthdays in January (since D5 contains 1) in the named range "birthdays" (B5:B104).

How this formula works

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 SUMPRODUCT instead.

Inside SUMPRODUCT, we have this expression:

MONTH(birthday)=D5)

The MONTH function extracts the month for each date in the named range "birthdays", and this is compared to the value in D5, which is 1. The result is an array of TRUE / FALSE values where each TRUE represents a date where month=1.

The TRUE FALSE values are then converted to ones and zeros with the double negative (--). SUMPRODUCT then sums these numbers and returns a final result.