Excel SUMIF function: add only the rows you want
SUMIF adds only the cells that meet a condition. =SUMIF(E2:E7,"East",B2:B7) adds the sales in column B for rows where column E says East. When the condition and the numbers are in the same column, you can leave off the last range: =SUMIF(B2:B7,">1000").
The examples use this small sales table, the same one in the practice box below.
| A Rep | B Sales | D Target | E Region | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | 1,000 | East |
| 3 | Luis | 830 | 1,000 | West |
| 4 | Priya | 2,100 | 1,000 | East |
| 5 | Marcus | 475 | 1,000 | North |
| 6 | Jen | 990 | 1,000 | West |
| 7 | Omar | 1,600 | 1,000 | East |
Syntax
=SUMIF(range, criteria, [sum_range])- range is where Excel checks the condition.
- criteria is the condition, in quotes if it's text or a comparison.
- sum_range is what to add. Leave it out to add the cells in range itself.
Examples
| Formula | Result |
|---|---|
=SUMIF(E2:E7,"East",B2:B7) | 4,950 |
=SUMIF(B2:B7,">1000") | 4,950 |
=SUMIF(E2:E7,"West",B2:B7) | 1,820 |
The first two happen to match here because every East sale is over 1,000.
The mistake most people make
They use SUM. =SUM(B2:B7) returns 7,245, the whole team. Tests love this question because the wrong answer looks reasonable.
Using a cell for the condition
Put the region name in a cell, like J2, and use =SUMIF(E2:E7,J2,B2:B7). For a comparison, join it: =SUMIF(B2:B7,">"&D2).
More than one condition
Use SUMIFS. Careful: its argument order is different.
Try it: East region sales
Add up the sales (column B) for reps in the East region (column E). Put the total in B8.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
What's the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the range to add last. SUMIFS takes many conditions and puts the range to add first.
Why does my SUMIF return 0?
Usually the text doesn't match exactly, often because of extra spaces, or the numbers are stored as text. Check the data with TRIM and make sure the numbers are real numbers.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How to use SUMIFS in Excel to add values that meet several conditions, how its argument order differs from SUMIF, and a live exercise.
How to use COUNTIF in Excel with numbers, text and cell references, plus COUNTIFS for two conditions and a live exercise.
How to use SUM in Excel with a real example, the mistakes that cost points on Excel tests, and a live exercise to practice.