Total per region, ready to fill down
A pivot table groups rows by a label, here Region, and adds up a value for each group, here Sales. You can build the same summary with formulas, and it updates the moment the data changes.
Click Continue when you're ready.
Already know it? Type the formula straight into F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Region | Total | ||
| 2 | North | Shoes | 420 | North | |||
| 3 | South | Shirts | 310 | South | |||
| 4 | North | Shirts | 150 | West | |||
| 5 | West | Shoes | 500 | ||||
| 6 | South | Shoes | 260 | ||||
| 7 | North | Shoes | 380 | ||||
| 8 | West | Shirts | 190 | ||||
| 9 | South | Shirts | 240 | ||||
| 10 |
Why this works
This is the "Sum of Sales by Region" a pivot table makes. The data ranges have $ on both the column and the row, so they stay put when you fill the formula down. E2 has no $, so in F3 it becomes E3 and the row totals South. North is 420 + 150 + 380 = 950.
Common mistake: Leaving the data ranges relative. The first row is right, but every row you fill down reads a range that has slid one row further.
The answer
- F2
=SUMIF(1$A$2:$A$9,2E2,3$C$2:$C$9)
In plain words: Add up 3the sales in C2:C9 on the rows where 1the region in A2:A9 is 2the region in E2.
+15 points