SUMIF With Multiple Criteria: How SUMIFS Works
· 6 min read
To add up numbers that meet more than one condition, use SUMIFS. Put the column you want to add first, then one pair of "range, criteria" for each condition. Every condition must be true for a row to be added. This is how SUMIFS multiple criteria formulas work in every version of Excel since 2007:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)For example, completed orders in the West region:
=SUMIFS(E2:E9, B2:B9, "West", D2:D9, "completed")The example table
Eight orders from a supplies company. Amounts are in dollars.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Category | Status | Amount | Question | Result |
| 2 | 1001 | North | Office | completed | 420 | West, completed | 1,100 |
| 3 | 1002 | West | Kitchen | completed | 310 | West, completed, Office | 560 |
| 4 | 1003 | West | Office | refunded | 150 | ||
| 5 | 1004 | South | Cleaning | completed | 275 | ||
| 6 | 1005 | West | Office | completed | 560 | ||
| 7 | 1006 | North | Kitchen | cancelled | 190 | ||
| 8 | 1007 | West | Cleaning | completed | 230 | ||
| 9 | 1008 | East | Office | completed | 380 |
Two criteria
=SUMIFS(E2:E9, B2:B9, "West", D2:D9, "completed")Result: 1,100. The West rows are 1002, 1003, 1005 and 1007. Order 1003 is refunded, so it drops out, leaving 310 + 560 + 230.
Three or more criteria
Just add another pair. Completed West orders in the Office category:
=SUMIFS(E2:E9, B2:B9, "West", D2:D9, "completed", C2:C9, "Office")Result: 560, only order 1005. SUMIFS accepts up to 127 pairs, so you will run out of ideas before you run out of criteria.
Criteria from a cell
Hard-coding "West" works for one answer. For a summary table, point at a cell instead. If G2 holds the region name:
=SUMIFS($E$2:$E$9, $B$2:$B$9, G2, $D$2:$D$9, "completed")The $ signs keep the data ranges fixed while G2 moves as you fill the formula down. Without them, every copy below looks at a different block of rows. Our percentage of total guide explains absolute references in more detail.
SUMIF vs SUMIFS
SUMIF handles exactly one condition. SUMIFS handles one or more. The bigger trap is that the arguments come in a different order:
=SUMIF(range, criteria, sum_range)
=SUMIFS(sum_range, criteria_range, criteria)In SUMIF the column to add is last. In SUMIFS it is first. If you switch between them, double-check the order.
West, with SUMIF:
=SUMIF(B2:B9, "West", E2:E9)Result: 1,250. That is 150 more than the SUMIFS answer, because SUMIF also added the refunded order 1003. Both formulas are "correct", they just answer different questions. If revenue only counts completed orders, the one-condition version is wrong.
You can always use SUMIFS, even with a single condition. Many analysts do, so they only have one argument order to remember.
Excel SUMIF multiple criteria with OR logic
SUMIFS joins its conditions with AND. To add West or East completed orders, add two SUMIFS together, or give one SUMIFS a list in curly braces and wrap it in SUM:
=SUMIFS(E2:E9, B2:B9, "West", D2:D9, "completed") + SUMIFS(E2:E9, B2:B9, "East", D2:D9, "completed")
=SUM(SUMIFS(E2:E9, B2:B9, {"West","East"}, D2:D9, "completed"))Both return 1,480: 1,100 for West plus 380 for East. The curly-brace version returns one total per item in the list, and SUM adds them up.
Only use OR across values of the same column like this. If you add two SUMIFS that test different columns, a row that meets both conditions gets counted twice.
SUMIFS not equal to
Use "<>" before the value. Everything except cancelled orders:
=SUMIFS(E2:E9, D2:D9, "<>cancelled")Result: 2,325, which is the full total of 2,515 minus the cancelled 190. To exclude a value held in a cell, join the operator to the cell with &:
=SUMIFS(E2:E9, D2:D9, "<>"&H2)Other comparison operators work the same way: ">500", "<=1000", or ">="&H3.
SUMIF not blank
A second small table: invoices, some still unpaid.
| A: Invoice | B: Paid on | C: Amount | |
|---|---|---|---|
| 2 | INV-01 | 2025-03-04 | 800 |
| 3 | INV-02 | 450 | |
| 4 | INV-03 | 2025-03-11 | 1,200 |
| 5 | INV-04 | 300 |
To add the paid ones, use "<>" on its own, which means "not empty":
=SUMIF(B2:B5, "<>", C2:C5)Result: 2,000. For the unpaid ones use "", which matches empty cells:
=SUMIF(B2:B5, "", C2:C5)Result: 750. Watch out for cells that look empty but contain a space. A space is a value, so that row counts as "not blank".
Between two dates
Dates are numbers in Excel, so you use two conditions on the same column. Revenue for March 2025, if dates are in column A and amounts in column C:
=SUMIFS(C2:C100, A2:A100, ">="&DATE(2025,3,1), A2:A100, "<"&DATE(2025,4,1))Using "before 1 April" instead of "on or before 31 March" means you never have to think about how many days a month has.
Common mistakes
Summing every status. This is the mistake AI assistants make most with revenue. Ask for "West revenue" and you will often get a one-condition SUMIF that includes refunded and cancelled orders, like the 1,250 above. The formula has no error, so nothing looks wrong. Before you trust a total, ask: which statuses does this include? Then check that the formula has a criteria pair for status. Paste it into the free Excel formula checker to see each criteria pair and the total it gives.
Sum range shifted by a row. =SUMIF(B2:B9, "West", E3:E10) does not raise an error. SUMIF lines the sum range up by its first cell, so each region is matched with the amount from the row below it. To spot it, read both ranges in the formula bar: they must start and end on the same row numbers. SUMIFS is stricter here: ranges of different sizes give #VALUE!, but a range that is the same size and shifted still gives a wrong number.
Ranges that move when filled down. Without $, a SUMIFS filled down a summary table checks rows 3 to 10, then 4 to 11, and so on. The quick test: the region totals should add up to the grand total.
Wrong argument order. Writing SUMIFS with the sum range last, out of SUMIF habit, usually gives #VALUE! or 0.
Quoting a cell reference. ">=H3" compares against the text "H3", not the value in H3. Use ">="&H3.
Text that almost matches. Criteria are not case sensitive, so "Completed" and "completed" match. A trailing space is different: "West " does not match "West". If a total looks low, check the source column for stray spaces.
FAQ
Can SUMIFS have multiple criteria in the same column?
Yes for AND conditions on one column, such as a date range: repeat the column with each condition. For OR conditions on one column, use SUM(SUMIFS(..., {"West","East"}, ...)) or add two SUMIFS together.
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the sum range last. SUMIFS takes one or more conditions and puts the sum range first. SUMIFS can do everything SUMIF does.
How do I use SUMIFS with "not equal to"?
Put <> in front of the value inside the quotes, for example "<>cancelled". For a value in a cell, use "<>"&H2.
Why does my SUMIFS return 0?
Usually a criterion does not match the data exactly: an extra space, a number stored as text, or a quoted cell reference such as "H2". Check one row by hand with a simple =B3="West" test.
How do I count instead of add?
Use COUNTIFS. It takes the same criteria pairs without the sum range. See COUNTIF with multiple criteria for examples.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Add up one region
- West revenue, completed only
- The AI’s revenue is too highAudit
- The AI's West total is offAudit