# How to count birthdays by month in Excel

Compare the month of each date, whatever the year: `=SUMPRODUCT(--(MONTH(B2:B12)=G1))`, with the month number in G1.

**Excel:** `=SUMPRODUCT(--(MONTH(B2:B12)=G1))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Employee | Date of birth |  |  |  | Month | 3 |
| Lena Park | 1988-03-14 |  |  |  | Birthdays |  |
| Omar Ruiz | 1992-07-02 |  |  |  |  |  |
| Noor Haddad | 1979-03-30 |  |  |  |  |  |
| Tom Becker | 1995-11-21 |  |  |  |  |  |
| Priya Shah | 1984-03-01 |  |  |  |  |  |
| Ines Lopez | 1990-12-25 |  |  |  |  |  |
| Jon Wells | 2001-02-28 |  |  |  |  |  |
| Mia Chen | 1987-03-17 |  |  |  |  |  |
| Raj Iyer | 1993-04-01 |  |  |  |  |  |
| Sara Nilsen | 1976-08-09 |  |  |  |  |  |
| Ali Kaya | 1999-10-31 |  |  |  |  |  |

Formula in G2: `=SUMPRODUCT(--(MONTH(B2:B12)=G1))`

Result: 4

## 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.

## Other ways

- `=SUMPRODUCT(--(TEXT(B2:B12,"mmmm")="March"))`: Compares the month name instead of the number. Day and month names follow your Excel language.
- `=ROWS(FILTER(B2:B12,MONTH(B2:B12)=G1))`: 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".

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-birthdays-by-month