# How to count dates by month in Excel

Put the first day of each month in a column and use COUNTIFS from that day to the month end: `=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&EOMONTH(E2,0))`.

**Excel:** `=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&EOMONTH(E2,0))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Order | Date |  |  | Month | Orders |
| B-2001 | 2026-01-05 |  |  | 2026-01-01 |  |
| B-2002 | 2026-01-19 |  |  | 2026-02-01 |  |
| B-2003 | 2026-01-31 |  |  | 2026-03-01 |  |
| B-2004 | 2026-02-02 |  |  | 2026-04-01 |  |
| B-2005 | 2026-02-27 |  |  |  |  |
| B-2006 | 2026-03-01 |  |  |  |  |
| B-2007 | 2026-03-09 |  |  |  |  |
| B-2008 | 2026-03-15 |  |  |  |  |
| B-2009 | 2026-03-31 |  |  |  |  |
| B-2010 | 2026-04-01 |  |  |  |  |
| B-2011 | 2026-04-12 |  |  |  |  |
| B-2012 | 2026-04-30 |  |  |  |  |

Formula in F2, filled to F5: `=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&EOMONTH(E2,0))`

Result: 3; 2; 4; 3

## How it works

Each month is a date range: from the first day in column E to `EOMONTH(E2,0)`, the last day of the same month. COUNTIFS counts the order dates inside that range. The order column is locked with $ so it stays put while E2 moves down to the next month.

Using real dates in column E, formatted to show only the month, keeps the year in the test, so January 2026 is never mixed with January 2025.

## Other ways

- `=SUMPRODUCT((MONTH($B$2:$B$13)=MONTH(E2))*(YEAR($B$2:$B$13)=YEAR(E2)))`: Compares month and year numbers instead of a range. Works in every version; slower on very large sheets.
- `=SUMPRODUCT(--(TEXT($B$2:$B$13,"yyyy-mm")=TEXT(E2,"yyyy-mm")))`: Compares a year-month label. Handy when the summary column holds labels like 2026-03 instead of dates.

## Common mistakes

- Testing only `MONTH(B2:B13)=3` counts March of every year. Include the year unless you want that.
- If the dates have times, `"<="&EOMONTH(E2,0)` misses entries after midnight on the last day. Use `"<"&EOMONTH(E2,0)+1`.

## Questions

**How do I count dates in one month only?**

Put that month's first day in a cell and use the same formula once: `=COUNTIFS(B2:B13,">="&E2,B2:B13,"<="&EOMONTH(E2,0))`.

**How do I count by month with a month name like "March" in the cell?**

Compare the month name: `=SUMPRODUCT(--(TEXT(B2:B13,"mmmm")=E2))`. This ignores the year.

**Can a pivot table do this?**

Yes: put the date in Rows, group it by Months and Years, and count the orders.

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-dates-by-month