SUMIF and SUMIFS: Conditional Totals Made Simple

SUMIF adds up numbers that meet one condition. SUMIFS does the same for several conditions at once — and takes its arguments in a different order.

=SUMIF(A2:A100, "Riyadh", C2:C100)

=SUMIFS(C2:C100, A2:A100, "Riyadh", B2:B100, "Hardware")

Look at where C2:C100 — the column being added — sits in each. In SUMIF it's last. In SUMIFS it's first. That single inconsistency causes more broken formulas than everything else about these two functions combined.

The argument order

Function	Order
SUMIF	where to look, what to look for, what to add
SUMIFS	what to add, where to look, what to look for, (repeat)

A useful habit: use SUMIFS even when you only have one condition. It works perfectly with a single criteria pair, and you stop having to remember which order you're in.

=SUMIFS(C2:C100, A2:A100, "Riyadh")

Same total, different order — SUMIF ends with the sum range, SUMIFS begins with it.

1. A total for one category

With regions in column A, categories in B and amounts in C:

=SUMIF($A$2:$A$100, "Riyadh", $C$2:$C$100)

Lock the ranges with dollar signs before you drag the formula down a summary list. Without them the ranges shift row by row and totals quietly go wrong — the same failure that catches people out with lookups.

2. Criteria that use an operator

Anything other than a plain equals goes in quotes, operator included:

=SUMIF($C$2:$C$100, ">1000")

Note there's no third argument here. When the column you're testing is also the column you're adding, SUMIF sums the range it searched.

The operators are the same set IF uses: >, <, >=, <=, <>. And the same boundary trap applies — ">1000" excludes exactly 1000, ">=1000" includes it.

3. Two conditions with SUMIFS

=SUMIFS($C$2:$C$100, $A$2:$A$100, "Riyadh", $B$2:$B$100, "Hardware")

Conditions are combined with AND, never OR. A row must satisfy every pair to be included. There's no OR option built in — for that you add two SUMIFS together:

=SUMIFS($C$2:$C$100, $A$2:$A$100, "Riyadh") + SUMIFS($C$2:$C$100, $A$2:$A$100, "Jeddah")

4. A date range

Two conditions on the same column — one for each end of the range:

=SUMIFS($C$2:$C$100, $D$2:$D$100, ">=2026-01-01", $D$2:$D$100, "<=2026-03-31")

Naming the same range twice is correct and expected. This is the standard way to total a quarter, a month, or any window.

5. Pointing criteria at a cell

Hard-coded criteria mean editing the formula every time the question changes. Referring to a cell is better — but the syntax catches everyone out.

A plain reference works on its own:

=SUMIFS($C$2:$C$100, $A$2:$A$100, F1)

But an operator plus a reference has to be joined with &:

=SUMIFS($C$2:$C$100, $C$2:$C$100, ">"&F1)

⚠️ ">F1" in quotes is not a reference. Excel reads it as the literal text "greater than F1" and returns 0 without complaint. The & is what turns the cell's value into part of the condition.

6. Partial matches with wildcards

Criteria	Matches
"North*"	Anything starting with North
"*Ltd"	Anything ending in Ltd
"*steel*"	Anything containing steel
"PRD-1??"	PRD-1 plus exactly two more characters

* stands for any number of characters, ? for exactly one.

7. Blanks and non-blanks

=SUMIFS($C$2:$C$100, $B$2:$B$100, "<>")

=SUMIFS($C$2:$C$100, $B$2:$B$100, "")

The first totals rows where the category is filled in. The second totals rows where it's missing — useful for finding how much value is sitting in uncategorised records before you clean a dataset.

Why it returns zero

SUMIF and SUMIFS rarely throw errors. They return 0, which is a valid-looking answer and therefore easy to miss. Four causes, in order of likelihood:

1. Trailing spaces

"Riyadh " is not "Riyadh". The formula finds nothing and reports 0. Clean the column first — this is the same invisible problem that breaks lookups and de-duplication.

2. Numbers stored as text

Text that looks like a number can't be summed. Look for the green triangle in the cell corner, then use Data → Text to Columns → Finish on the column.

3. An operator without the ampersand

">F1" instead of ">"&F1, as in example 5.

4. Arguments in the wrong order

A SUMIFS written in SUMIF order — criteria range first — usually returns 0 or a nonsense total rather than an error.

And one that does error: #VALUE!

In SUMIFS every range must be the same height. $C$2:$C$100 paired with $A$2:$A$50 fails. Check the row numbers match.

When to stop using SUMIFS

Situation	Use
One condition, quick answer	SUMIF
Two or more conditions	SUMIFS
OR logic across categories	Two SUMIFS added together
Conditions that multiply columns together	SUMPRODUCT
Many totals across many groupings	A pivot table

If a summary sheet is filling up with dozens of SUMIFS formulas, that's usually a pivot table waiting to happen.

Does this work in Google Sheets?

Yes. Same names, same argument orders, same wildcards. Sheets also has QUERY, which handles grouped totals in a single formula, but SUMIFS behaves identically in both programs.

Common questions

Can SUMIF handle two conditions?

No. That's what SUMIFS exists for. Add an S and move the sum range to the front.

Can I sum across two columns at once?

Not in one SUMIFS. Either add two formulas together, or restructure the data so the values sit in a single column — the second option is almost always better.

Is SUMIFS case-sensitive?

No. "riyadh" and "Riyadh" are treated as the same. Use SUMPRODUCT with EXACT if case matters.

Why is my total higher than expected?

Usually a wildcard you didn't intend. If a category is literally named with an asterisk or question mark, prefix it with a tilde: "~*".

The short version

• SUMIF puts the sum range last; SUMIFS puts it first.
• Use SUMIFS for everything and the order stops being a question.
• Operators live inside the quotes: ">1000".
• Cell references need &: ">"&F1.
• SUMIFS conditions are always AND. For OR, add two together.
• A zero result usually means trailing spaces or text-formatted numbers, not an empty dataset.

Totals answer how much. The next question is usually how many — which is what COUNTIF and COUNTIFS are for, and they follow exactly the same argument-order rules.