Count Birthdays in a Month in Excel

by

To count how many dates of birth fall in July, compare the month of each date with 7: =SUMPRODUCT(--(MONTH(C2:C17)=7)). The same idea counts birthdays this month, or lists who they are.

FormulaSUMPRODUCT + MONTH
Excel 365FILTER to list the names
Works inAll versions

Count birthdays in a month

=SUMPRODUCT(--(MONTH($C$2:$C$17)=7))
SUMPRODUCT and MONTH counting July birthdays
Step Result
MONTH($C$2:$C$17) {12;7;11;3;1;8;…}, the month of each date
=7 TRUE for each July date
-- Turns TRUE/FALSE into 1/0
SUMPRODUCT Adds them: 3

With the month number in E10: =SUMPRODUCT(--(MONTH($C$2:$C$17)=E10)).

With the month name

=SUMPRODUCT(--(TEXT($C$2:$C$17,"mmmm")=E10))
Counting birthdays for the month named in a cell

This works when E10 holds July. The month names come from your Windows language, so a file shared with someone using Hindi or French settings may return 0. MONTH with a number works everywhere.

Birthdays this month

=SUMPRODUCT(--(MONTH($C$2:$C$17)=MONTH(TODAY())))

Updates automatically every month. Useful for a staff or student birthday reminder.

List the names (Excel 365)

=FILTER(B2:B17,MONTH(C2:C17)=MONTH(TODAY()),"No birthdays")

Wrap it in SORTBY to order by day: =SORTBY(FILTER(B2:C17,MONTH(C2:C17)=7),DAY(FILTER(C2:C17,MONTH(C2:C17)=7))).

Things to know

  • The dates must be real dates, not text. If MONTH returns #VALUE!, convert them with Data → Text to Columns → Date.
  • COUNTIF can't count by month, because it can't apply MONTH to a range. Use SUMPRODUCT, or a helper column with =MONTH(C2) and COUNTIF on that.
  • To highlight this month's birthdays, use the conditional formatting rule =MONTH($C2)=MONTH(TODAY()).

Related