How to count dates by month in Excel
Formula (Excel and Google Sheets)
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&EOMONTH(E2,0))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)).
- Result on the example
- one result per row in
F2, filled down toF5 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F5. Change any input and the result updates, or edit the formula to experiment.
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.
=COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&EOMONTH(E2,0))Result3
$B$2:$B$1312 rows × 1 col: 1/5/2026, 1/19/2026, 1/31/2026, 2/2/2026, …A range: 12 cells in one column.
E2Jan 2026The value in E2.
">="&E2">=46023"The & operator joins as text.
EOMONTH(E2,0)1/31/2026 (46053)EOMONTH: Last day of the month, months away.- start_date
E2Jan 2026 - months
0
- start_date
"<="&EOMONTH(E2,0)"<=46053"The & operator joins as text.
COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&EOMONTH(E2,0))3COUNTIFS: Counts rows that meet every condition.- criteria_range1
$B$2:$B$1312 rows × 1 col: 1/5/2026, 1/19/2026, 1/31/2026, 2/2/2026, … - criteria1
">="&E2">=46023" - criteria_range2
$B$2:$B$1312 rows × 1 col: 1/5/2026, 1/19/2026, 1/31/2026, 2/2/2026, … - criteria2
"<="&EOMONTH(E2,0)"<=46053"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS($B$2:$B$13,">="&E2,$B$2:$B$13,"<="&EOMONTH(E2,0)). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((MONTH($B$2:$B$13)=MONTH(E2))*(YEAR($B$2:$B$13)=YEAR(E2))) | 3, 2, 4… | 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"))) | 3, 2, 4… | 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)=3counts 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.
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 dates by month
F2, count the orders dated in the month that starts on E2, then fill down to F5.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.
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.