How to count birthdays by month in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(MONTH(B2:B12)=G1))Compare the month of each date, whatever the year: =SUMPRODUCT(--(MONTH(B2:B12)=G1)), with the month number in G1.
- Result on the example
- 4 in
G2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
Birth dates span many years, so a date range won't work. MONTH pulls out just the month number from each date, comparing it with G1 gives TRUE for the March birthdays, and SUMPRODUCT counts them.
COUNTIF can't do this because it can't apply MONTH to each cell.
=SUMPRODUCT(--(MONTH(B2:B12)=G1))Result4
B2:B1211 rows × 1 col: 3/14/1988, 7/2/1992, 3/30/1979, 11/21/1995, …A range: 11 cells in one column.
MONTH(B2:B12){11 rows × 1 col} 3, 7, 3, 11, …MONTH: Month of a date, 1–12.- serial_number
B2:B1211 rows × 1 col: 3/14/1988, 7/2/1992, 3/30/1979, 11/21/1995, …
- serial_number
G13The value in G1.
MONTH(B2:B12)=G1{11 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
-(MONTH(B2:B12)=G1{11 rows × 1 col} -1, 0, -1, 0, …Makes the value negative.
--(MONTH(B2:B12)=G1{11 rows × 1 col} 1, 0, 1, 0, …Makes the value negative.
SUMPRODUCT(--(MONTH(B2:B12)=G1))4SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(MONTH(B2:B12)=G1{11 rows × 1 col} 1, 0, 1, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(MONTH(B2:B12)=G1)). We ran it in Google Sheets mode on the same data and it returns 4.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(TEXT(B2:B12,"mmmm")="March")) | 4 | Compares the month name instead of the number. Day and month names follow your Excel language. |
| =ROWS(FILTER(B2:B12,MONTH(B2:B12)=G1)) | 4 | Excel 2021 or later. Returns an error, not 0, if nobody has a birthday that month. |
Common mistakes
- Birth dates stored as text (for example "14.03.1988" from another system) make MONTH return #VALUE!, and SUMPRODUCT fails. Convert them to dates first.
- Use the month number, not the name, in G1 with this formula: MONTH returns 3, not "March".
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count birthdays by month
G2, count the employees whose birthday falls in the month number in G1.Questions
How do I count birthdays this month?
Use the current month: =SUMPRODUCT(--(MONTH(B2:B12)=MONTH(TODAY()))).
How do I list who has a birthday this month?
In Excel 365 or Google Sheets: =FILTER(A2:A12,MONTH(B2:B12)=G1).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.