Conditional formula page
COUNTIFS by Month Formula
Use this when you need a monthly count such as tasks, orders, tickets, or rows.
Count rows by month with COUNTIFS.
If F1 contains a January 2026 date, this counts January 2026 rows.
Copy formulas
=COUNTIFS(A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)=COUNTIFS(A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)Excel and Google Sheets use the same COUNTIFS month-boundary pattern. The formula derives the first day and the first day of the next month from any real date in F1.
Example data
| Date | Region | Product | Amount | Rep |
|---|---|---|---|---|
| 2026-01-04 | East | Widget | 420 | Maya |
| 2026-01-12 | West | Widget | 310 | Noah |
| 2026-02-03 | East | Gadget | 275 | Maya |
| 2026-02-15 | East | Widget | 640 | Iris |
If F1 contains a January 2026 date, this counts January 2026 rows.
How the formula works
- The lower boundary is the first day of the selected month.
- The upper boundary is before the first day of the next month.
- The count ignores amounts and only counts matching dates.
| Syntax piece | Role in the formula |
|---|---|
| DATE(YEAR(F1),MONTH(F1),1) | Builds the first calendar day of F1's month for the inclusive lower boundary. |
| EOMONTH(F1,0)+1 | Builds the first day after F1's month as an exclusive upper boundary, including final-day timestamps. |
| A2:A100 | The Date range counted; it is not an Amount range. |
Verified examples
=COUNTIFS(A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)Excel: Enter any January 2026 date in F1; the sample Date range has two January rows. Returns: 2
=COUNTIFS(A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1)Google Sheets: Enter any February 2026 date in F1; the sample Date range has two February rows. Returns: 2
=COUNTIFS(A2:A100, ">="&DATE(YEAR(F1),MONTH(F1),1), A2:A100, "<"&EOMONTH(F1,0)+1, B2:B100, "East")Excel: Enter any January 2026 date in F1 and use East as the Region criterion. Returns: 1
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| Changing F1 does not change the monthly count | F1 contains a month label or date-looking text rather than a real spreadsheet date. | Enter a real date such as 2026-01-15 and format it to display the month if needed. |
| January records from several years are combined | A MONTH-only test ignores YEAR when comparing dates. | Keep both full DATE(YEAR(F1),MONTH(F1),1) and EOMONTH(F1,0)+1 boundaries. |
| Records late on the final day are missing | The upper boundary is the final date at midnight while source cells contain timestamps, or criteria ranges are misaligned. | Use the exclusive next-month boundary and keep every criteria range aligned to rows 2 through 100. |
When not to use this formula
- Do not use this formula when the desired result is an Amount total; use SUMIFS with the same month boundaries instead.
Alternatives
| Alternative | When to use it |
|---|---|
| COUNTIFS Between Dates Formula | Use when the start and end dates are selected independently rather than derived from one month cell. |
| COUNTIFS Formula Builder | Use when the month condition must be combined with configurable criteria. |
Related formulas
Official references
- COUNTIFS function from Microsoft
- EOMONTH function from Microsoft
FAQ
Can F1 contain any date in the month?
Yes. YEAR and MONTH extract the month and year from any real date in F1; it does not have to be the first day.
Why is the upper test less than the next month?
An exclusive next-month boundary includes every time value on the final day without relying on a midnight end-date comparison.