Excel track · Exercise 8 of 86 Paid
Sum functions summary
Combine SUM and SUMIF to total a column, a subset of it, and the remainder.
Advanced
Regional sales with two reps
Read-only data · screen 1 of 2 · answer in B7
In B7, total everything sold, and then be ready for the two questions that follow.
| A | B | |
|---|---|---|
| 1 | Region | Amount |
| 2 | North | 320 |
| 3 | South | 250 |
| 4 | North | 180 |
| 5 | West | 300 |
| 6 | North | 400 |
| 7 | Everything |
In C7, total everything that is not North: one SUM, one SUMIF, subtracted.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Amount |
| 2 | North | Ada | 320 |
| 3 | South | Ben | 250 |
| 4 | North | Cleo | 180 |
| 5 | West | Dev | 300 |
| 6 | North | Ada | 400 |
| 7 | Outside North |
Reference answer
What it does
The group asks three questions of one column: what the whole of it comes to, what the rows you care about come to, and what is left over. SUM takes the first, SUMIF the second with one condition, SUMIFS the second with two or more.
The second screen answers the third by subtraction: 1450 in the column minus 900 of North sales leaves 550 elsewhere. SUMIF with a not-equal condition reaches the same figure in one call, and the subtraction keeps each half free to take a different filter later.
Two habits carry across the group. The sum range and the criteria range line up row for row, and a condition is text in quotes even when it looks like a number.
Syntax
=SUM(range) and =SUMIF(criteria_range, condition, sum_range), combined by subtractionWorked example
On Regional sales with two reps, =SUM(B2:B6) returns 1450.
=SUM(B2:B6)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | A range in the call holds text where numbers were expected, or a comparison has lost its quotes. |
#NAME? | SUMIF was typed as SUMIFS with SUMIF arguments, or the function is misspelled. |
Wrong total | Ranges of different shapes match the wrong rows. The total is off by one row. |
At work
A regional report that needs the grand total, the headline region and everything outside it on three lines.