Excel SUMIFS Formula: Sum With Multiple Conditions
SUMIFS adds values only for rows that satisfy every supplied condition. It is useful for a report that needs to filter on more than one field.
What to do
- Identify the numeric range to total, then list each criteria range and its matching condition.
- Use the copyable example below to total North-region orders with Paid status; check the small data table to verify the result.
- Keep the sum range and each criteria range the same height and aligned to the same rows.
- Compare a result against a filtered view or a small manually checked sample before using it in a report.
Checks and limitations
- Unlike SUMIF, SUMIFS places the sum range first.
- All criteria are combined with AND logic; a row must meet every condition.
What SUMIFS does
Use SUMIFS to total spreadsheet values only when every specified condition is met.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Copyable formula
=SUMIFS(C2:C5,B2:B5,"North",D2:D5,"Paid")Example result: 270.
Example data
| Item | Region | Sales | Status |
|---|---|---|---|
| Widget | North | 120 | Paid |
| Cable | South | 80 | Paid |
| Widget | North | 200 | Pending |
| Desk | North | 150 | Paid |
Real-world use
Total only paid sales orders for a particular region while excluding pending orders.
Common mistakes
- Putting the sum range after criteria ranges; SUMIFS uses the sum range first.
- Using ranges with different sizes or shifted starting rows.
Alternative formulas
SUMIF handles one condition. A PivotTable can summarize multiple dimensions interactively.
Compatibility
SUMIFS is available in modern Excel versions and Excel for the web; very old spreadsheet software may not support it.
Frequently asked questions
Are all SUMIFS conditions combined?
Yes. A row must satisfy every supplied condition to be included.
Can criteria use cell references?
Yes. Reference cells directly; combine comparison operators and references with concatenation when required.
Browse the Excel formula library ยท Open the percentage calculator