SUM over two separate ranges
Add two blocks of figures without dragging the subtotal between them into the total.
Task:
The sheet lists the two shops one under the other, with each shop's subtotal already in place. Put the combined sales of both shops in B10 — the six product lines only, not the subtotals.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of SUM
0/4The same formula in the other shapes it takes at work.
- Basic SUM Function
A total is the first thing anyone asks a spreadsheet for.
CoreBeginner - SUM over two separate ranges
Add two blocks of figures without dragging the subtotal between them into the total.
Beginner - A running total with SUM
One formula, filled down, that shows the balance after every line.
Intermediate - SUM across a row and over a block
Totals run sideways as often as they run down.
Beginner
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | |
|---|---|---|
| 1 | Line | Sales |
| 2 | Market St — coffee | 1840 |
| 3 | Market St — pastries | 960 |
| 4 | Market St — subtotal | 2800 |
| 5 | Quay — coffee | 2210 |
| 6 | Quay — pastries | 1175 |
| 7 | Quay — subtotal | 3385 |
| 8 | ||
| 9 | ||
| 10 | Both shops |
What this exercise teachesMay contain the answer
A single range over B2:B7 double-counts: every subtotal is already the sum of the lines above it, so the total would come out at twice the real figure. Listing the ranges you mean, with a comma between them, keeps the subtotals out without deleting them from the sheet.