Excel SUMIFS Function — One Formula for Conditional Totals (and Why SUMIF Is a Trap)

TL;DR — SUMIFS adds up a column, but only the rows that pass every condition you give it. Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, …). The single biggest gotcha: SUMIF and SUMIFS put their arguments in opposite order — SUMIF tests first and sums last; SUMIFS sums first. Copy a formula from one to the other and you silently total the wrong column. The fix is a habit: use SUMIFS for everything, even one condition. To compare against a cell, concatenate the operator — ">"&G1, never ">G1". Multiple criteria are joined with AND; for OR, pass an array and wrap in SUM. Works in Excel 2007+.

=SUMIFS(Amount, Region, "West")
=SUMIFS(Amount, Region, "West", Amount, ">"&G1)

Almost every "sum with a condition" you'll ever write is really the same question: of all these rows, total the ones that match. Excel gives you two functions for it, and the older one — SUMIF — is a trap that has cost people correct numbers for fifteen years. This guide is built around one strong recommendation, then the criteria rules that make conditional totals actually work.

What you'll learn

• The mental model: SUMIFS is a WHERE clause that ends in a total
• Why SUMIF and SUMIFS reverse their arguments — and why you should drop SUMIF
• The criteria mini-language: operators live in quotes, ">"&cell compares to a cell
• AND is built in; OR needs an array and a SUM wrapper
• How to sum between two dates without breaking on locale
• The modern move: spill a per-category summary with SUMIFS + UNIQUE

The mental model: a WHERE clause that ends in a total

If you think in SQL, SUMIFS is SELECT SUM(amount) … WHERE region = 'West' AND amount > 1000. You name the column to total, then stack up tests; Excel keeps the rows where all the tests pass and adds their values. Everything that confuses people about SUMIFS disappears once you hold that picture: the first argument is the thing you add, and every pair after it is a filter.

' "Total the Amount column, for rows where Region is West"
=SUMIFS(Amount, Region, "West")

' Add a second filter — now both must be true (AND)
=SUMIFS(Amount, Region, "West", Product, "Widgets")

Each criteria_range must be the same height as sum_range — they're parallel columns of the same table. Mismatch the shapes and you get #VALUE!.

The rule that saves you: stop using SUMIF

Here is the trap, and it's worth more than any other tip on this page. The two functions order their arguments backwards from each other:

' SUMIF — the range you TEST comes first, the range you SUM is last (optional)
=SUMIF(Region, "West", Amount)

' SUMIFS — the range you SUM comes FIRST, then test pairs
=SUMIFS(Amount, Region, "West")

Read those twice. In SUMIF, Amount is the third argument; in SUMIFS, it's the first. People learn SUMIF, then need a second condition, switch to SUMIFS, and paste the ranges in the old order — now Excel sums Region (text, so it totals to a confusing number or zero) and treats Amount as a criteria range. No error. Just a wrong total that looks plausible.

The judgment call is blunt: forget SUMIF exists. SUMIFS does everything SUMIF does, the argument order never changes whether you have one condition or ten, and you'll never get bitten by the swap. The only thing SUMIF "saves" is typing one comma. That's not a reason to risk a silent error in a finance model.

The criteria mini-language: operators live in quotes

The argument labelled criteria is a tiny string language, and it trips up everyone the first time they try to compare against a cell instead of a fixed value. Three rules cover almost all of it:

=SUMIFS(Amount, Amount, ">1000")        ' literal threshold — fine as plain text
=SUMIFS(Amount, Amount, ">"&G1)          ' compare to a CELL — concatenate the operator
=SUMIFS(Amount, Region, "West*")         ' wildcard — any region starting "West"

The killer mistake is ">G1". That doesn't mean "greater than the value in G1" — it searches for rows literally equal to the text "G1", finds none, and returns 0. The operator (>, <, >=, <>) must sit inside the quotes, and any cell reference must be outside, joined with &. Once that clicks, ">="&TODAY(), "<>"&G1, and "*"&G1&"*" all read naturally. Text matching is case-insensitive, and * (any characters) / ? (one character) are your wildcards — escape a literal asterisk with ~*.

AND is free; OR you have to build

Stacking criteria pairs gives you AND — every test must pass. There is no "OR" argument. The clean way to total rows matching either of several values is an array criteria wrapped in SUM, because SUMIFS with an array of criteria returns one total per value, and SUM adds them up:

' AND — built in: West AND Widgets
=SUMIFS(Amount, Region, "West", Product, "Widgets")

' OR — array criteria + SUM: West OR East
=SUM(SUMIFS(Amount, Region, {"West","East"}))

This matters because the naive fix — adding two separate SUMIFS — is fine for two values but doesn't scale, and it quietly double-counts if your OR conditions can both be true on the same row. The SUM(SUMIFS(…{…}…)) pattern handles a list of any length and is the standard idiom once you've seen it.

Summing between two dates (the locale-safe way)

A date range is just two criteria on the same date column — a lower bound and an upper bound. Never type the date as text (">01/06/2026" is read differently in the US and Europe and may not match at all). Build the date with DATE so it's unambiguous everywhere:

=SUMIFS(Amount, OrderDate, ">="&DATE(2026,1,1), OrderDate, "<="&DATE(2026,3,31))

Same column, two tests, AND between them — that's a quarter's total. Swap the literals for cell references (">="&H1) and you have a live, parameterised report.

The modern move: spill a whole summary table

This is where conditional totals meet dynamic arrays. Feed UNIQUE as the criteria, and a single SUMIFS spills a total for every category at once — no dragging, no helper list:

=SUMIFS(Amount, Region, UNIQUE(Region))    ' one total per distinct region, spilled

Put =UNIQUE(Region) in one cell and the SUMIFS beside it, and you've built a self-updating summary that grows as new regions appear. For genuinely complex extracts you might reach for FILTER and add the result — but for "total by category," SUMIFS over a UNIQUE spill is the leanest tool there is, and far faster on big sheets than an array of SUMPRODUCT.

The judgment call: SUMIFS vs SUMPRODUCT vs a PivotTable

If you're still writing SUMPRODUCT((Region="West")*(Amount)) for multi-column conditions, that pattern predates SUMIFS — it's slower and harder to read, and the only reason to keep it is when you need to multiply two columns and filter (a weighted total). If you're building the same summary by hand every month, a PivotTable is the better home for exploratory slicing. SUMIFS wins when you want a specific number, live, embedded in a model — which is most of the time.

How ExcelMaster helps

Most real SUMIFS work is phrased in business terms, not formula terms: total revenue for the West region in Q1, above the approval threshold in cell G1. ExcelMaster turns that sentence into the formula — correct argument order, ">"&G1 concatenation, DATE()-based bounds — and drops it where you want it. You describe the number you need; it handles the criteria grammar that makes SUMIFS finicky to type by hand.

Frequently asked questions

Why is my SUMIF returning the wrong total (or zero)?

Almost always the argument order. SUMIF(range, criteria, sum_range) tests first and sums last; SUMIFS(sum_range, range, criteria) sums first. If you moved a formula between them without reordering, Excel is summing the wrong column. Switch everything to SUMIFS and the order is always sum-range-first.

How do I use SUMIFS with multiple criteria?

Add more criteria_range, criteria pairs — they're joined with AND, so every test must pass: =SUMIFS(Amount, Region, "West", Product, "Widgets"). Each criteria range must be the same height as the sum range.

How do I do an OR condition with SUMIFS?

Pass the alternatives as an array and wrap in SUM: =SUM(SUMIFS(Amount, Region, {"West","East"})). This avoids the double-counting you'd get from simply adding two separate SUMIFS formulas.

How do I sum between two dates?

Use two criteria on the date column with DATE() so it's locale-safe: =SUMIFS(Amount, OrderDate, ">="&DATE(2026,1,1), OrderDate, "<="&DATE(2026,3,31)).

Does SUMIFS work in Excel 2016 or older?

Yes. SUMIFS has shipped since Excel 2007, and SUMIF since the 1990s. Both work in every supported version — so there's no compatibility reason to keep using the older, trap-prone SUMIF.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-24.

Related guides: Excel COUNTIFS · Excel AVERAGEIFS · Excel FILTER · Excel UNIQUE