SUMIFS is easy to write and surprisingly easy to get wrong when dates are involved. A formula that looks correct returns zero, or totals the wrong month, because Excel is comparing a real date with text, a timestamp, or a date serial that only looks like a date.
These six patterns cover almost every date-range question in an assignment or a work report. Each one is copy-paste ready, and the checklist at the end catches the traps before you submit.
The syntax, in the right order
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
The sum range comes first. Every criteria range must be the same height and width as the sum range. If you reverse the first two arguments, Excel may return a number — just the wrong one — which is worse than an error.
1. Between two hard-coded dates
The safest way to write a date is with DATE, which cannot be misread by a different locale:
=SUMIFS(D2:D1000, A2:A1000, ">="&DATE(2026,1,1), A2:A1000, "<="&DATE(2026,1,31))
Column A holds order dates and column D holds sales. The formula adds January sales. Note that both comparisons use the same date column — that is normal in SUMIFS.
2. Between two dates stored in cells
For a reusable report, put the start date in G2 and the end date in G3:
=SUMIFS(D2:D1000, A2:A1000, ">="&G2, A2:A1000, "<="&G3)
Format G2 and G3 as dates. If they are text, the comparison silently fails. A quick test: a real date moves to the right when the cell is formatted as General; a text date stays left-aligned.
3. A whole month with EOMONTH
Hard-coding the last day is fragile — February changes. Use EOMONTH to calculate it:
=SUMIFS(D2:D1000, A2:A1000, ">="&DATE(2026,2,1), A2:A1000, "<="&EOMONTH(DATE(2026,2,1),0))
For a month selected in a cell, replace DATE(2026,2,1) with G2 and point the end date at EOMONTH(G2,0). This is the cleanest pattern for monthly reports and dashboards.
4. A whole year
=SUMIFS(D2:D1000, A2:A1000, ">="&DATE(2026,1,1), A2:A1000, "<="&DATE(2026,12,31))
Use this when the brief asks for annual totals. If you need a year selected in a cell, use DATE(G2,1,1) and DATE(G2,12,31) so the formula still builds real dates.
5. A rolling window, such as the last 30 days
=SUMIFS(D2:D1000, A2:A1000, ">="&TODAY()-30, A2:A1000, "<="&TODAY())
This recalculates when the workbook opens. For a fixed reporting date, replace TODAY() with a cell reference so the numbers stop moving. Mention the reporting date in your notes — markers dislike totals that change between screenshots.
6. Count rows in a date range with COUNTIFS
The same logic counts orders, students or tickets:
=COUNTIFS(A2:A1000, ">="&G2, A2:A1000, "<="&G3)
Pair it with SUMIFS to show both the count and the total. A report with “42 orders, $18,450” is more useful than a total on its own.
Why your SUMIFS returns 0
- The dates are text. Imported CSV dates often arrive as text. Select the column, use Data → Text to Columns → Finish, or convert with
DATEVALUE. - The date column contains timestamps. A value of 1-Jan-2026 14:30 is greater than 1-Jan-2026 00:00. For an inclusive end date, compare with
"<"&G3+1or strip the time withINT(A2)in a helper column. - The ranges are different sizes. SUMIFS requires matching dimensions. One stray row in the sum range shifts every result.
- The operator is inside the wrong quotes. Write
">="&G2, not">=G2". The second version looks for the literal text “>=G2”. - The date is ambiguous. 03/04/2026 is March 4 in the US and April 3 in the UK. Use DATE or a date picker, and state the locale in your report.
A five-minute assignment checklist
- Convert the date column to real dates and check the alignment.
- Confirm every criteria range matches the sum range exactly.
- Test one month against a manual filter and compare the totals.
- Label the start and end date cells, and format them as dates.
- Add a note explaining inclusive dates and the reporting date.
Build it without typing the formula
The free Excel Formula Generator builds SUMIF, SUMIFS, COUNTIF and IF formulas from your ranges and copies them with absolute references. If a formula already exists but returns the wrong answer, the Formula Checker finds syntax and range problems without uploading your workbook.
For more conditional-counting patterns, read the COUNTIF and SUMIFS guide. If the dates arrived as messy text, the Data Cleaner trims spaces and standardises values before you build the report.
Need the workbook built and explained before the deadline? Send the file to an Excel expert for a fixed quote.
Before you submit, paste the date-range formulas into the Workbook Health Check and run the Assignment Readiness Check. Mismatched criteria ranges and text dates are two of the most common reasons a correct-looking SUMIFS returns the wrong total.
