SUMIF - Add only matching rows
Total revenue for one branch or expenses above a threshold, without adding rows that do not match your condition.
By the end, you can
- Separate the condition range from the amount range.
- Write text and numeric criteria.
- Check row alignment before totaling.
Before you start: Understand SUM and cell ranges. Cell References helps with fixed ranges.
In this lesson
One condition, one total
A regional lead wants Mumbai revenue, not all branch revenue. SUMIF tests a condition for each row and adds the corresponding amount only for matching rows. Use it for one department's expenses, one customer's invoices, or transactions above a threshold. It returns a total, not the number of records.
Syntax and arguments
=SUMIF(range, criteria, [sum_range])The test range comes first. The amount range is optional only when you want to add the tested cells themselves.
| Argument | Purpose | Example |
|---|---|---|
rangeRequired | Cells to test against the condition. | A2:A6 |
criteriaRequired | A label, comparison, or cell containing the requested value. | "Mumbai" or D2 |
sum_rangeOptional | Aligned amounts to add. If omitted, range is also the amount range. | B2:B6 |
A branch revenue summary
₹22,000| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Branch | Revenue | Selected branch | Total | |
| 2 | Mumbai | ₹12,000 | Mumbai | ₹22,000 | |
| 3 | Delhi | ₹8,000 | |||
| 4 | Mumbai | ₹6,000 | |||
| 5 | Pune | ₹9,000 | |||
| 6 | Mumbai | ₹4,000 |
Labels, thresholds, and exclusions
=SUMIF(A2:A6,"<>Mumbai",B2:B6)The <> operator means not equal. Delhi and Pune contribute 8,000 + 9,000.
=SUMIF(B2:B6,">=9000")The amount range is also the test range, so the third argument can be omitted. Include 12,000 and 9,000.
=SUMIF(A2:A6,"Mu*",B2:B6)* matches any sequence of characters; all Mumbai rows match this prefix.
How criteria work
- Quote literal text and comparisons, such as "Mumbai" and ">=9000". For a threshold stored in a cell, join the operator to the address: ">="&D2.
- Text matching is not case-sensitive. * matches any text sequence; ? matches one character; ~ escapes a literal wildcard.
- No matching rows returns 0. A matching numeric zero also contributes zero, so inspect records when those cases matter.
- Use aligned ranges with matching size and shape. Excel can expand a mismatched SUMIF sum range from its starting cell, which can silently total unexpected cells.
Common mistakes and fixes
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.