Featured image
SUMIFS in Excel
SUMIFS adds up the rows that satisfy every one of several conditions: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …). The column being added comes first, which is the opposite of SUMIF, and every condition has to hold on the same row for that row to count.
=SUMIFS(sum_range, criteria_range1, criteria1, …)
SUMIFS is SUMIF with as many conditions as you like, and it is the one worth learning first despite the extra letter. It handles a single condition perfectly well, so writing SUMIFS everywhere means never having to remember which of the two functions puts the sum range first.
Its one genuine subtlety is that the conditions are joined by and, not or. Every criteria range is checked against its own criterion on the same row, and the row is included only if all of them pass. Totalling the North region plus the South region is not a SUMIFS; it is two SUMIFS added together.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- The cells to add. First, unlike SUMIF. Every criteria range that follows must be the same height as this one.
- The first column to test.
- What counts as a match in that column. Same rules as SUMIF: "North" for text, 12 for a number, ">10" for a comparison with the operator inside the quotes, ">"&C2 to compare against a cell.
- optional
- Further pairs. Each is checked on the same row as the others, and a row counts only when every pair passes.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
Two conditions at once
=SUMIFS(D2:D7, B2:B7, "East", C2:C7, ">3")Returns 1200
Two reps are in the East region, Dara and Fay. Only Dara also has units above 3, because Fay's units cell is empty and a blank is not greater than three. So the total is Dara's 1200 alone, and the blank quietly changed the answer.
One condition, written as SUMIFS anyway
=SUMIFS(D2:D7, F2:F7, "A")Returns 12400
Perfectly valid with a single pair, and the reason to use SUMIFS as the default. The sum range is first here and last in the equivalent SUMIF, which is the whole argument for picking one function and sticking to it.
A category and a numeric threshold
=SUMIFS(D2:D7, B2:B7, "South", C2:C7, ">=7")Returns 7350
Blake with 7 units and Eli with 15 are both in the South and both clear the threshold, so both are counted: 2100 plus 5250. Note >= rather than >, which is what keeps Blake's exactly-7 in the total.
Two text conditions
=SUMIFS(D2:D7, B2:B7, "North", F2:F7, "A")Returns 12400
Both North reps happen to be grade A, so this matches the grade-A total above. When two different conditions produce the same number it is worth checking that one of them is actually doing something.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
The conditions are joined by and
Every pair has to hold on the same row. There is no way to express or inside a single SUMIFS, and attempting it by listing two criteria against the same column produces zero, because no row can be both North and South at once.
=SUMIFS(D2:D7, B2:B7, "North") + SUMIFS(D2:D7, B2:B7, "South")Returns 19750. For or, add two SUMIFS together. For more than a handful of alternatives, a helper column or a PivotTable is less painful than a formula that grows by a clause each time.
What blanks do to the answer
A blank cell does not satisfy a numeric comparison. It is not greater than three and it is not less than three; it is simply not a number, and the row drops out of the total. That is usually right, and it is almost always surprising the first time, because the row is plainly there on screen.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=SUMIFS(D2:D7, B2:B7, "East", C2:C6, ">3") + "x"WhySUMIFS seldom errors by itself. A #VALUE! here is usually the arithmetic around it, or, in Excel, criteria ranges that are not all the same height as the sum range.
FixCheck every range ends on the same row. Then evaluate the SUMIFS alone in a spare cell to see whether the fault is inside it or outside.
=SUMIFS(D2:D7, B2:B7, "North")/COUNTIFS(B2:B7, "West")WhyA total divided by a count of matching rows, where no rows matched. There is no West region, so the denominator is zero.
FixWorth leaving visible rather than hiding: it says the category is empty. Wrap it in IFERROR only once you have confirmed that an empty category is expected rather than a misspelled one.
What goes wrong
These are the mistakes that come up again and again with SUMIFS. Each one is reasonable. That is why people make it.
Writing the sum range last, out of SUMIF habit.
WhyThe two functions genuinely disagree about argument order, and nothing about either name hints at which is which.
InsteadSUMIFS puts the column being added first. If you use SUMIFS for everything, including single conditions, the question stops arising.
Expecting two criteria on one column to mean or.
WhyIn English, North and South describes two regions you want.
InsteadEvery pair must hold on the same row, so a single SUMIFS with both returns zero. Add two SUMIFS together instead.
Leaving the quotes off a text criterion.
WhyThe word looks like a word, not like a piece of text needing punctuation.
InsteadQuote it: "East". Excel reports this as #NAME?; the grid here simply matches nothing and returns zero, which is the same mistake wearing a disguise.
Ranges of different heights.
WhyEach range is selected separately, and one drag going a row too far is invisible afterwards.
InsteadEvery criteria range must match the sum range exactly. Excel returns #VALUE! for this; some other spreadsheets return a wrong number.
Copying the formula down without absolute references.
WhyThe first row is correct.
InsteadLock every range with dollar signs. A SUMIFS has three or more ranges in it and all of them slide.
Common questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition with the sum range last. SUMIFS takes any number of conditions with the sum range first. SUMIFS is a superset, so there is no case where SUMIF is required.
Can SUMIFS use OR logic?
Not within one formula. Each criteria pair must hold on the same row. For or, add separate SUMIFS together, or build a helper column that flags the rows you want and test that.
How do I use SUMIFS with a date range?
Two pairs against the same date column, one for each end: =SUMIFS(D2:D7, E2:E7, ">="&DATE(2024,1,1), E2:E7, "<="&DATE(2024,12,31)). Build the boundary with & rather than typing a date inside quotes, which is read as text.
Why does SUMIFS return 0?
No row satisfied every condition. Test the criteria one at a time by deleting pairs until a number appears; the pair you removed last is the one that matches nothing. Blanks and numbers stored as text are the usual reasons.