The formula
Share of total (fill down)ExcelGoogle Sheets
=C2/SUM($C$2:$C$7)In E2 → 16.22%
Region's share of totalExcelGoogle Sheets
=SUMIFS($C$2:$C$7,$A$2:$A$7,A2)/SUM($C$2:$C$7)In F2 → 50.68%
✓ Tested. All 7 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | |||
| 2 | East | Pens | 120 | 16.22% | 50.68% | |
| 3 | West | Pens | 95 | 12.84% | ||
| 4 | East | Paper | 180 | 24.32% | ||
| 5 | North | Pens | 60 | 8.11% | ||
| 6 | West | Paper | 210 | 28.38% | ||
| 7 | East | Ink | 75 | 10.14% |
How it works
- C2
- This row's value.
- SUM($C$2:$C$7)
- The total, locked with $.
Common errors and fixes
Why does the formula return #DIV/0! after filling down?
The total range moved because it wasn't locked.
Shares don't add to 100%?
Rounding in the display; the underlying values do.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.