SUMIFS()
Sums cells that meet multiple conditions at once.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)SUMIFS works like SUMIF but supports any number of conditions, all of which must be true for a row to count — it's AND logic across every criteria pair.
Note the argument order is different from SUMIF: sum_range comes first in SUMIFS, but last in SUMIF. Mixing the two up is a common source of #VALUE! errors.
SUMIFS is the right default for conditional totals, including with a single condition, because the sum range comes first and the argument order stays consistent as conditions are added. Two details matter in practice. Every criteria range must be exactly the same height as the sum range, or the formula returns #VALUE! — a problem that appears the moment someone mixes a whole-column reference with a bounded one. And operators are concatenated to their values: ">="&DATE(2026,1,1) works, ">=DATE(2026,1,1)" compares against a literal string and silently matches nothing.
Arguments
- sum_range
- The range to sum.
- criteria_range1
- The first range to test.
- criteria1
- The condition for criteria_range1.
- criteria_range2, criteria2, ...optional
- Additional range/condition pairs — all must match.
Examples

=SUMIFS(D2:D200, A2:A200, "North", B2:B200, "2026")Sums column D where region is North AND year is 2026.
=SUMIFS(D2:D500, B2:B500, ">="&DATE(2026,1,1), B2:B500, "<"&DATE(2026,4,1))A date range needs two criteria on the same column, and the operator must be concatenated to the date — ">="&DATE(...), not ">=DATE(...)".
=SUMIFS(D2:D500, A2:A500, F2, C2:C500, "<>Cancelled")<> excludes a value. Note it excludes only that value — blank status rows still count, which is usually not what people expect.
Notes and gotchas
- The sum range comes first, then criteria pairs — the opposite of
SUMIF. This catches nearly everyone at some point. - Multiple criteria are combined with AND. For OR across values in one column, add two SUMIFS, or use an array constant:
=SUM(SUMIFS(amt, region, {"North","South"})). - SUMIFS cannot test a calculated condition such as
MONTH(date)=3. Add a helper column (ayyyy-mmperiod key works well) or useSUMPRODUCT. - Comparison criteria against a cell need concatenation:
">="&$B$1. Writing">=B1"compares against the literal text "B1". - Every criteria range must be the same size as the sum range, or you get
#VALUE!. - No match returns
0, not an error, so zero and "nothing matched" look identical. Pair withCOUNTIFSwhen the distinction matters.
Common errors
| You see | Because | Fix |
|---|---|---|
| #VALUE! | A criteria range is a different size from the sum range. | Use Table column references so every range is bounded by the same data. |
| Returns 0 | A criterion does not match, commonly a date compared as text or a key with trailing whitespace. | Replace the formula with COUNTIFS using the same criteria to confirm whether anything matches at all. |
SUMIFS tricks, tips, and workbook hacks
SUMIFS is the core aggregation engine for financial P&Ls, KPI scorecards, and regional sales summaries that slice raw transaction tables without a PivotTable. Putting sum_range first makes it natural to read: 'Sum column D where column A is North AND column B is Q1.'
Because all criteria pairs in SUMIFS are combined with AND logic, adding a date-window pair (">="&StartDate, "<="&EndDate) against the same date column is the cleanest way to sum transactions by month, quarter, or fiscal year.
Quick SUMIFS checks before trusting the result
- Bound date ranges with two criteria pairs on the same date column: DateCol, ">="&EOMONTH(G1,-1)+1, DateCol, "<="&EOMONTH(G1,0).
- Format your transaction data as an Excel Table (tblLedger) so structured references like tblLedger[Amount] guarantee every range has identical row counts.
- To sum multiple values from the same column (OR logic), pass an array constant {"North","South"} or wrap SUM(SUMIFS(..., H2:H4)).
- Never reference external closed workbooks—SUMIFS returns #VALUE! unless the external source file is open in Excel.
| SUMIFS pattern | SUMIFS formula | Best use | Watch out |
|---|---|---|---|
| Sum transactions within a calendar month | =SUMIFS(D2:D1000,A2:A1000,">="&G1,A2:A1000,"<="&EOMONTH(G1,0)) | Monthly P&L columns where G1 holds the first day of the month (e.g. 2026-03-01) and column A holds daily dates. | If column A includes time-of-day timestamps, use "<"&(EOMONTH(G1,0)+1) for the upper bound. |
| OR criteria across one field + AND criteria on another | =SUM(SUMIFS(D2:D1000,B2:B1000,{"Hardware","Software"},C2:C1000,"Enterprise")) | Summing Enterprise deals that belong to either Hardware or Software. | Only one criteria argument can hold a horizontal array constant unless the second is transposed. |
| Dynamic measure + dynamic criteria with INDEX | =SUMIFS(INDEX(D2:G1000,0,MATCH(J1,D1:G1,0)),A2:A1000,I2) | Letting a dashboard dropdown (J1) switch the summed column between Revenue, COGS, Gross Profit, and Units. | INDEX(..., 0, col) hands a true 1-column range reference to SUMIFS. |
| Wildcard optional filter ("All" dropdown support) | =SUMIFS(D2:D1000,B2:B1000,IF(H1="All","*",H1)) | Dashboard slicers where choosing "All" should include every non-blank text value in column B. | "*" matches any text string but skips numeric or blank cells in B2:B1000. |
| SUMIFS mistake | What happens in SUMIFS | Fix |
|---|---|---|
| Testing two categories on the same column with two separate pairs | =SUMIFS(D:D, B:B, "North", B:B, "South") always returns 0 because no single row is simultaneously North AND South. | Use =SUM(SUMIFS(D:D, B:B, {"North","South"})) for OR logic on the same column. |
| Timestamps on the end date of a date range | Orders placed on March 31 at 2:15 PM (serial 46112.59) are excluded by "<="&DATE(2026,3,31) (serial 46112.00). | Use strictly less than the next day: "<"&(EndDate+1). |
SUMIFS practice drills
- Build a 3-row by 4-column regional/quarterly summary grid populated by a single mixed-reference SUMIFS formula dragged across and down.
- Add a time component (14:30) to a transaction on the last day of the month and verify why "<"&(EOMONTH(G1,0)+1) catches it while "<="&EOMONTH(G1,0) misses it.




