A cross-table with mixed references
PremiumBuild a table with regions down and products across. In F3, use SUMIFS for the region in E3 and the product in F2, with $E3 and F$2 so the formula can be filled down and across.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | |||||
| 2 | North | Shoes | 420 | Shoes | Shirts | |||
| 3 | South | Shirts | 310 | North | ||||
| 4 | North | Shirts | 150 | South | ||||
| 5 | West | Shoes | 500 | West | ||||
| 6 | South | Shoes | 260 | |||||
| 7 | North | Shoes | 380 | |||||
| 8 | West | Shirts | 190 | |||||
| 9 | South | Shirts | 240 | |||||
| 10 |
Premium lesson
Unlock Summary tables and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for budgeting and planning
Why this works
A cross-table is what a pivot table gives you with one field in Rows and one in Columns. The region labels run down column E, so $E3 locks the column and lets the row change. The product labels run along row 2, so F$2 locks the row and lets the column change. North Shoes is 420 + 380 = 800.
Common mistake: Locking the labels completely, like $E$3. Every cell of the table then shows North Shoes.
The answer
- F3
=SUMIFS(1$C$2:$C$9,2$A$2:$A$9,3$E3,4$B$2:$B$9,5F$2)
In plain words: Add up 1the sales in C2:C9 on the rows where 2the region in A2:A9 is 3the region in column E and 4the product in B2:B9 is 5the product in row 2.