SUM across a row and over a block
Totals run sideways as often as they run down.
Task:
The quarterly sheet has one row per product and one column per month. In E2:E4 give each product's quarter total, and in E5 the grand total of every cell in the block.
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 | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Jan | Feb | Mar | Quarter |
| 2 | Notebooks | 420 | 385 | 510 | |
| 3 | Pens | 1310 | 1180 | 1265 | |
| 4 | Folders | 240 | 275 | 230 | |
| 5 | All products |
What this exercise teachesMay contain the answer
Summing the block B2:D4 rather than the three row totals means the grand total does not depend on E2:E4 being right. If someone overtypes one of them with a number, the two totals stop agreeing, which is exactly how you would notice.