Maths and conditional totals

Excel SUMIFS Function: Totals With Two or More Conditions

Adds numbers only where every one of several conditions is true at once.

SUMIFS is SUMIF for the real world, where you rarely filter on just one thing. Sales in the North region, in the Hardware category, above £500 — SUMIFS takes all three conditions and adds only the rows where every one of them holds.

The important detail is that it reverses SUMIF's argument order. SUMIFS puts the range you are adding first, then pairs of range and criteria after it. This trips up almost everyone who learned SUMIF first, and it is the reason many people simply use SUMIFS everywhere, even for a single condition — one argument order to remember instead of two.

Conditions combine with AND, never OR. Every pair you add narrows the result further. To total rows matching either of two things, add two SUMIFS together.

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Arguments

sum_range
Required
The numbers to add. First, unlike SUMIF, where it comes last and is optional.
criteria_range1
Required
The first range to test. Must be the same size as sum_range.
criteria1
Required
What that range must match. Same forms as SUMIF: values, comparisons in quotes, cell references, wildcards.
criteria_range2, criteria2, ...
Optional
Further pairs, up to 127 of them. Every pair must hold for a row to be included.

The example data

Headers in row 1, data in A2:D6.

ABCD
1RegionRepCategoryAmount
2NorthAliceHardware1240
3SouthBrunoSoftware385
4NorthChenHardware2100
5SouthAliceSoftware940
6NorthBrunoSoftware156

Worked examples

=SUMIFS(D2:D6, A2:A6, "North", C2:C6, "Hardware")

Result: 3340

Two conditions. Only the two rows that are both North and Hardware are added.

=SUMIFS(D2:D6, A2:A6, "South", B2:B6, "Alice")

Result: 940

A single row satisfies both conditions.

=SUMIFS(D2:D6, A2:A6, "North", D2:D6, ">500")

Result: 3340

The sum range can also be a criteria range — North rows worth more than 500, which excludes Bruno's 156.

=SUMIFS(D2:D6, C2:C6, "Software")

Result: 1481

One condition, written as SUMIFS. Note the range order is the opposite of the equivalent SUMIF.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: SUM with Multiple Values

Common errors and how to fix them

#VALUE!

Why it happens: One of the criteria ranges is a different size from sum_range. SUMIFS is strict about this where SUMIF is not.

How to fix it: Make every range exactly the same height. Whole-column references (D:D, A:A) are the easy way to guarantee it.

Returns 0

Why it happens: The conditions are mutually exclusive, or one of them does not match the stored data — the usual text-versus-number and trailing-space problems.

How to fix it: Remove conditions one at a time until it returns something. The one that changes the answer to 0 is the broken one.

Only the first condition seems to apply

Why it happens: Arguments were supplied in SUMIF's order, so what you meant as sum_range was read as criteria_range1.

How to fix it: SUMIFS starts with the range you are adding. If your formula starts with the range you are testing, it is written as a SUMIF.

Tips worth knowing

  • For a date range, use two conditions on the same column: =SUMIFS(D:D, E:E, ">="&F1, E:E, "<="&F2).
  • Reference criteria from cells rather than typing them, so the sheet becomes a small report the reader can drive.
  • Comparison operators must be quoted and joined with & when they involve a cell: ">"&F1, never >F1.
  • For an OR condition, add SUMIFS calls together: =SUMIFS(...,"North") + SUMIFS(...,"South").

Frequently asked questions

Why is the argument order different from SUMIF?

Because SUMIFS accepts an unlimited number of criteria pairs, the sum range has to come first — there is no fixed position at the end for it to occupy. It is an unfortunate inconsistency, and the simplest response is to use SUMIFS for everything.

Can SUMIFS handle OR conditions?

Not within one formula: every criteria pair narrows the result with AND. To total rows matching either condition, add two SUMIFS together, or use SUMPRODUCT for anything more complicated.

How do I sum between two dates?

Use two conditions on the date column, one for each end: =SUMIFS(D:D, E:E, ">="&F1, E:E, "<="&F2), where F1 and F2 hold the start and end dates. Putting the dates in cells rather than in the formula keeps them editable.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions