SUMIFS: totals that follow your reporting rules
Monthly reports usually have more than one condition: the right region, a particular status and a date window that includes the whole final day. SUMIFS can handle that, but only when the ranges and boundaries agree.
All criteria are AND conditions: a row must match the region, status and date window. The September 30 timestamp contributes 50 because it is earlier than October 1. Using <=September 30 instead would exclude times later than midnight on that date. The Closed row contributes nothing even though its region and date match.
| Formula | Expected result / interpretation |
|---|---|
| =SUMIFS(D2:D6,A2:A6,F2,B2:B6,”Open”,C2:C6,”>=”&G2,C2:C6,”<“&H2) | 170 |
| =COUNTIFS(A2:A6,F2,B2:B6,”Open”,C2:C6,”>=”&G2,C2:C6,”<“&H2) | 2 qualifying orders |
| Row | A: Region | B: Status | C: Order date | D: Amount |
|---|---|---|---|---|
| 2 | East | Open | 2026-09-01 | 120 |
| 3 | East | Closed | 2026-09-04 | 80 |
| 4 | West | Open | 2026-09-07 | 200 |
| 5 | East | Open | 2026-09-30 15:00 | 50 |
| 6 | East | Open | 2026-10-01 | 90 |
Place these rows in A1:D6, using the displayed headers in row 1. Enter each date with DATE(year,month,day), not as ambiguous day/month text. Put East in F2, =DATE(2026,9,1) in G2 and =DATE(2026,10,1) in H2.
Total open orders inside a reporting month
Formula syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
What goes into it
sum_range
Cells whose values will be added.
criteria_range1
First range to test.
criteria1
Condition applied to the first criteria range.
additional criteria
Optional range/criteria pairs for more rules.
A quick example first
=SUMIFS(D:D,A:A,H2,B:B,"Open")
Sums column D where column A matches H2 and column B is Open.
=SUMIFS(D:D,C:C,">="&J2,C:C,"<="&K2)
Sums D for dates in C between J2 and K2.
Before you copy this into a real sheet
Check the selected rows as well as the final total: two mistakes can cancel each other numerically. Ensure amounts are numbers with ISNUMBER and date cells are real serials. Lock the ranges with dollar signs when copying the formula. Do not add separate SUMIFS results for overlapping conditions without checking for double counting. For a live row-by-row extract, use FILTER; for a total controlled by several independent criteria, SUMIFS is the simpler choice.
Useful habits
- Keep every criteria range aligned to the same rows as the sum range.
- Build comparison criteria with operators joined to cells, such as “>=”&J2.
- Put changing business thresholds in cells instead of hard-coding them repeatedly.
Where people get caught out
- Using ranges with different starting rows or sizes.
- Forgetting that dates are numeric serials and comparing them with ambiguous text dates.
Other functions worth a look
A detail worth remembering
For reports with timestamps, the first day of the following month makes a safer upper boundary than “less than or equal to the last day” at midnight.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.