Conditional totals
SUMIFS by month: date criteria that do not break next year
Monthly reporting formulas should not need to be rewritten every month. A reliable SUMIFS by month formula uses a start date and an end boundary, not a text label that only works in one layout.
Short answer
Use >= first day of the month and < first day of the next month. This handles real dates cleanly and avoids mistakes with month names, year changes, and time values.
Core pattern
Use a lower date boundary and an upper date boundary
The strongest SUMIFS pattern for monthly totals uses two criteria against the date column: one for the first day of the month and one for the first day of the next month. The second condition should usually be less than the next month, not less than or equal to the last day of the current month. That detail matters when imported data includes time values.
If the report month is stored in F1 as a real date such as 2026-05-01, the formula can use F1 for the month start and EOMONTH(F1,0)+1 for the next month boundary. This makes the formula reusable across reporting periods.
Monthly revenue from a date column
=SUMIFS(C:C,A:A,">="&F1,A:A,"<"&EOMONTH(F1,0)+1)Adds values in C:C for rows whose dates in A:A fall inside the month stored in F1.
Why not month names
Text month labels work until the report grows
A helper column with month names can be fine for a small tracker, but it becomes fragile when the data covers multiple years. January 2025 and January 2026 are different reporting periods, even if both rows say January. Text month labels also depend on language and spelling, which creates extra risk in shared files.
Real date boundaries are more precise. They work across years, sort correctly, and make the formula easier to review. If you want a visible month label for users, format the date cell as mmm yyyy or mmmm yyyy rather than converting the logic to text.
| Approach | Good for | Risk |
|---|---|---|
| Month name helper | Small single-year lists | January from different years can mix together |
| Month number helper | Simple internal trackers | Still needs a year condition |
| Date boundaries | Recurring reports | Requires valid real dates in the source data |
Reference cells
Put the report month in a cell so the formula can be reused
Hard-coding dates into formulas makes maintenance harder. If a dashboard has twelve monthly totals, put the first day of each month in header cells and let every SUMIFS formula refer to the relevant header. The displayed format can still show May 2026 or May, but the underlying value should be a real date.
This approach also makes copy-across formulas safer. A formula in G2 can point to G1 for the month, while H2 points to H1. You update the headers once and the report logic remains consistent.
Copy-across monthly formula
=SUMIFS($C:$C,$A:$A,">="&G$1,$A:$A,"<"&EOMONTH(G$1,0)+1)Locks the data columns and lets the month header change as the formula is copied across.
Troubleshooting
When monthly SUMIFS returns zero, inspect the source dates first
A monthly SUMIFS formula can return zero even when rows are visible for that month. The usual cause is that the date column is stored as text. Sorting may look odd, date formatting may not apply, or the formula may treat the value as text instead of a real date serial.
Before changing the SUMIFS logic, test one source date with ISNUMBER. A real Excel date returns TRUE because it is stored as a number. If the date is text, fix the import or add a helper column that converts the value safely before using it in reporting formulas.
- Check whether the date cells are real dates, not text.
- Confirm the report month cell is also a real date.
- Use < next month instead of <= last day when time values may exist.
- Avoid month-name criteria unless the data is intentionally single-year.
FAQ
Frequently asked questions
Why use < next month instead of <= end of month?
The < next month pattern includes all dates and times inside the month without accidentally excluding rows that contain time values.
Can I use this in Google Sheets?
Yes. The same date-boundary logic works well in Google Sheets when the date column contains real dates.
What if my dates are stored as text?
Fix or convert the source dates first. SUMIFS date criteria are much more reliable when the date column stores real date values.
Next pages
Use the matching tool when you need a custom formula
These pages turn the guide decision into spreadsheet output that matches your platform, ranges, and task.
SUMIF and SUMIFS Formula Generator
Total amounts by one or many conditions for reports, dashboards, and trackers.
Google Sheets Formula Generator
Build Google Sheets formulas with Sheets-first syntax and shared-sheet workflows in mind.
Excel Formula Generator
Start from a plain-English task and get a full Excel formula you can adapt.
Formula Examples
Browse copy-ready reference formulas before generating something more custom.