For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=FUNCTIONS

SUMIFS()

Math & Trig

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

Excel worksheet with a Region/Year/Sales table and a SUMIFS formula totaling North sales for 2026, with a callout about argument order differing from SUMIF.
=SUMIFS(D2:D200, A2:A200, "North", B2:B200, "2026")
→142,900

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))
→62,940

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")
→21,450

<> 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 (a yyyy-mm period key works well) or use SUMPRODUCT.
  • 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 with COUNTIFS when the distinction matters.

Common errors

What goes wrong with SUMIFS, and why
You seeBecauseFix
#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 0A 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.
Tips / Tricks / Hacks

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 tricks and formula patterns
SUMIFS patternSUMIFS formulaBest useWatch 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.
Common SUMIFS mistakes and how to fix them
SUMIFS mistakeWhat happens in SUMIFSFix
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 rangeOrders 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

  1. Build a 3-row by 4-column regional/quarterly summary grid populated by a single mixed-reference SUMIFS formula dragged across and down.
  2. 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.

Related functions