Excel track · Exercise 3 of 86 Paid
SUMIF: a sum range on another column
Total one column while testing a different column for the condition.
Intermediate
Regional amounts and bonuses, and a list of scores
Read-only data · screen 1 of 2 · answer in C7
In C7, total the North bonus from column C, testing the region in column A.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Amount | Bonus |
| 2 | North | 320 | 32 |
| 3 | South | 250 | 25 |
| 4 | North | 180 | 18 |
| 5 | West | 300 | 30 |
| 6 | North | 400 | 40 |
| 7 | North bonus |
In B7, total the scores above 40 by letting SUMIF add the cells it tested.
| A | B | |
|---|---|---|
| 1 | Item | Score |
| 2 | A-1 | 40 |
| 3 | A-2 | 35 |
| 4 | B-1 | 52 |
| 5 | B-2 | 38 |
| 6 | B-3 | 47 |
| 7 | Scored over 40 |
Reference answer
What it does
SUMIF's third argument is the range to add. Leave it out and SUMIF adds the cells it has just tested, which suits a column of scores and fails the moment the numbers sit somewhere else.
Column A holds the region and column C the bonus, so the condition reads A2:A6 while the addition reads C2:C6. The ranges only have to line up row for row: row 2 of each is the same sale, and the North bonuses of 32, 18 and 40 come to 90.
A shifted range gives a wrong total instead of an error, so check that both ranges start and end on the same rows. The second screen works with two arguments because there the scores are both the test and the total.
Syntax
=SUMIF(criteria_range, condition, [sum_range])Worked example
On Regional amounts and bonuses, and a list of scores, =SUMIF(A2:A6,"North",C2:C6) returns 90.
=SUMIF(A2:A6,"North",C2:C6)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The sum range holds text where numbers were expected, which happens when the region column is given as the third argument. |
#NAME? | The function is spelled SMIF, or SUMIFS was used with the argument order SUMIF keeps. |
Silent 0 | Two arguments means SUMIF adds the criteria range. With words in that column the answer is 0, and no error marks it. |
At work
A commission sheet where the region decides who is paid and the bonus column carries the money.