# How to calculate percent of subtotal for each group in Excel

Divide each row by its group's total from SUMIF: `=C2/SUMIF($A$2:$A$10,A2,$C$2:$C$10)`.

**Excel:** `=C2/SUMIF($A$2:$A$10,A2,$C$2:$C$10)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Region | Rep | Sales | Share of region |
| East | Ana | 48000 |  |
| East | Ben | 32000 |  |
| East | Cara | 20000 |  |
| West | Dev | 45000 |  |
| West | Eli | 30000 |  |
| North | Fay | 26000 |  |
| North | Gus | 39000 |  |
| North | Hana | 15000 |  |
| North | Ivo | 20000 |  |

Formula in D2, filled to D10: `=C2/SUMIF($A$2:$A$10,A2,$C$2:$C$10)`

Result: 48.0%; 32.0%; 20.0%; 60.0%; 40.0%; 26.0%; 39.0%; 15.0%; 20.0%

## How it works

SUMIF adds up the sales of every row in the same region as the current row, so each rep is divided by their own region's subtotal. Ana sold 48,000 of East's 100,000: 48%. The shares within each region add up to 100%.

The rows don't need to be sorted by region; SUMIF finds the group wherever its rows are.

## Other ways

- `=C2/SUMPRODUCT(($A$2:$A$10=A2)*$C$2:$C$10)`: The same group total with SUMPRODUCT, which takes extra conditions such as a year.

## Common mistakes

- Without $ on the ranges, they shrink as you fill down and later rows divide by part of their group.
- A region spelled two ways forms two groups. Clean the labels first.

## Questions

**How do I show each region's share of the grand total too?**

Divide the region total by the overall total: `=SUMIF($A$2:$A$10,A2,$C$2:$C$10)/SUM($C$2:$C$10)`.

**Can a pivot table show percent of subtotal?**

Yes: Show Values As, % of Parent Row Total.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/percent-of-group-subtotal