How to Add Values by Condition With SUMIF
SUMIF adds values associated with rows that match one condition. Keep the criteria range and sum range aligned row by row.
What to do
- Use
=SUMIF(A2:A100,"North",B2:B100)to add values in B where the corresponding A cell is North. - For a threshold stored in D2, use
=SUMIF(A2:A100,">"&D2,B2:B100). - If the criteria range and sum range are the same, the sum-range argument can be omitted.
- Compare the result with a filtered subset of the same rows to confirm the criteria behave as intended.
Checks and limitations
- Mismatched range sizes or starting rows can produce a plausible but incorrect result.
- For two or more conditions, use SUMIFS and note that its sum range is the first argument.
What SUMIF does
Use SUMIF to add values for matching rows, with an example that totals sales for the North region.
Syntax
=SUMIF(range, criteria, [sum_range])
Copyable formula
=SUMIF(B2:B5,"North",C2:C5)Example result: 470.
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 sales by region when each order row contains a region and a sales amount.
Common mistakes
- Using criteria and sum ranges with different row alignment.
- Forgetting quotation marks around a text criterion.
Alternative formulas
Use SUMIFS for two or more conditions. Use a PivotTable for flexible summaries across many categories.
Compatibility
SUMIF is supported in current Excel versions. The formula uses the first argument as the criteria range and the third as the optional sum range.
Frequently asked questions
Can SUMIF test more than one condition?
Use SUMIFS when a total must meet multiple conditions.
Can the condition be a cell reference?
Yes. Use the cell reference as the criteria argument, or join an operator to a reference when needed.
Browse the Excel formula library ยท Open the percentage calculator