Each region's share of the total
PremiumIn F2, work out the share of all sales for the region in E2, as a decimal like 0.25: the region's SUMIF total divided by SUM($C$2:$C$9).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Region | Share | ||
| 2 | North | Shoes | 420 | West | |||
| 3 | South | Shirts | 310 | North | |||
| 4 | North | Shirts | 150 | South | |||
| 5 | West | Shoes | 500 | ||||
| 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
West sells 500 + 190 = 690 of a total 2450, so its share is 690 / 2450 = 0.28. This is the pivot table's "Show Values As: % of Grand Total". Both the region total and the grand total use locked ranges, so the formula fills down without changes, and the shares add up to 1.
Common mistake: Leaving the grand total unlocked. Filled down, SUM(C3:C10) misses the first row and the shares no longer add up to 100%.
The answer
- F2
=SUMIF($A$2:$A$9,E2,$C$2:$C$9)/SUM($C$2:$C$9)