The SUMIFS function in Excel
SUMIFS adds the numbers in rows where all conditions are true: paid orders from the North, sales of one product in one month, expenses of one team above a limit.
The range to add comes first, followed by range/condition pairs. Because it also works with a single condition, many people use SUMIFS everywhere instead of SUMIF.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
| Argument | What it means |
|---|---|
| sum_range | The cells to add. |
| criteria_range1 | The first range to check. |
| criteria1 | The condition for that range: "North", ">=100", a cell, or a wildcard. |
| criteria_range2, criteria2optional | More range/condition pairs. All ranges must be the same size as sum_range. |
Examples
1. Two conditions: region and status
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | North, paid | |
| 2 | 1001 | North | 250 | Paid | 325 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
North orders that are Paid: 250 + 75 = 325. Order 1003 is North but still Open, so it's left out.
2. Amounts between two limits
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Sum 100–300 | |
| 2 | 1001 | North | 250 | Paid | 380 | |
| 3 | 1002 | South | 90 | Paid | ||
| 4 | 1003 | North | 420 | Open | ||
| 5 | 1004 | East | 130 | Paid | ||
| 6 | 1005 | North | 75 | Paid |
The amount column is both the sum range and checked twice: 250 + 130 = 380.
3. Conditions from cells
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Status | Region | North | |
| 2 | 1001 | North | 250 | Paid | Status | Open | |
| 3 | 1002 | South | 90 | Paid | Total | 420 | |
| 4 | 1003 | North | 420 | Open | |||
| 5 | 1004 | East | 130 | Paid | |||
| 6 | 1005 | North | 75 | Paid |
Open North orders: only 1003 with 420. Change G1 and G2 to build a small report.
Common mistakes
Writing it like SUMIF, with the sum range at the end.
Fix: In SUMIFS, sum_range is always the first argument.
#VALUE! error. A criteria range has a different size than sum_range.
Fix: Make every range cover the same rows, e.g. all from row 2 to row 6.
Expecting OR logic. All conditions must be true at the same time.
Fix: For North or East, add two results:
=SUMIFS(C2:C6,B2:B6,"North")+SUMIFS(C2:C6,B2:B6,"East").
Frequently asked questions
SUMIF or SUMIFS?
Both give the same result for one condition. SUMIFS has a consistent order and grows easily to more conditions, so it's a good default.
How do I sum by date range?
Use two conditions on the date column, e.g. ">="&G1 and "<="&G2 with the start and end dates in G1 and G2.