How to calculate percentage of subtotal in Excel

The formula: value / SUMIF of its group

The sheet lists ten sales reps, the region each one works in, and their sales for the quarter. The question is not "what share of the company did Ana sell" but "what share of North did Ana sell". So the bottom of the fraction has to change with the region: North reps divide by the North total, South reps by the South total.

SUMIF does that. It adds up column C for every row whose region matches B2, so for Ana it returns the North total of 40,000 (18,400 + 12,600 + 9,000). Ana's 18,400 divided by that is 46%. Fill the formula down and Ben's row divides by the South total of 27,600 instead, without you writing a separate formula per region.

Two of the three arguments get dollar signs: the region range $B$2:$B$11 and the sales range $C$2:$C$11 must stay put as you fill. The criteria B2 must not be locked, because it has to become B3, B4 and so on, picking up each row's own region.

The rows do not need to be sorted

The regions in this list are mixed up: North, South, North, East, South... That is fine. SUMIF scans the whole locked range every time, so it finds all three North rows wherever they sit. You do not need to sort by region first, and adding a new rep at the bottom works too, as long as the ranges reach that row (or the data is an Excel Table, so the ranges grow by themselves).

This is the advantage over the subtotal approach many people try first: sorting by region, inserting a subtotal row under each group, and dividing by that cell. It works until someone re-sorts by sales or adds a row, and then the formulas point at the wrong subtotal. SUMIF does not care about order.

It is also easy to show both views side by side. Ana is 46% of North, but =C2/SUM($C$2:$C$11) shows she is 18.9% of the company's 97,600, and North as a whole is 41% of the company. Label the columns clearly ("Share of region", "Share of total"), because the two look alike and mean very different things.

Guard against groups with a zero total

Hana joined the West region this quarter and has no sales yet, so the West total is 0. The plain formula divides 0 by 0 and shows #DIV/0!. One brand-new rep is enough to put an error in the column, and an error in the column breaks any SUM or chart built on it.

Test the group total first and leave the cell empty when it is zero. Column D in the sample uses this version. It returns an empty string for West and the normal share everywhere else. A blank Sales cell on its own is not a problem: it counts as 0, so that rep shows 0% while the rest of the group still adds to 100%.

IFERROR(C2/SUMIF(...),"") is shorter, but it also hides real mistakes, such as a sales figure typed as text with a stray character. The IF version only catches the one case you expect.

Check that every group adds up to 100%

A quick check catches unlocked ranges, a group whose name is spelled two ways, and ranges that stop a row short. Add up the share column per region; every region with sales should come to exactly 1. In Excel 365 (HSTACK is not in Excel 2021), one formula lists every region next to its total.

In older versions, type the region names in a small table and use =SUMIF($B$2:$B$11,F2,$D$2:$D$11) next to each one.

If a region shows less than 100%, look for a variant name: "North " with a trailing space is a separate group to SUMIF, so those rows divide by their own little total and the real North no longer adds to 100%. Fix the data with TRIM or Find and Replace rather than the formula.

Why a group shows 99% after rounding

South has three reps with 9,200 each, so each one is exactly a third: 0.3333. Formatted as 0%, each shows 33%, and a reader adding the column gets 99%. The values underneath still add to exactly 1, which is what the check above shows. Only the display is rounded.

It goes wrong for real when you round the values themselves, for example with ROUND(...,2) inside the formula, and then add them. The sum of the three rounded shares really is 0.99. Keep the full value in the cell and round only with the number format, or show one decimal place (33.3%). If a report must show whole percents that add to 100, adjust the largest share by hand and footnote it.

The same thing in one formula, or with a PivotTable

In Excel 365 and 2021, =IFERROR(C2:C11/SUMIF(B2:B11,B2:B11,C2:C11),"") spills a share for every row at once. Giving SUMIF a whole range as its criteria makes it return one group total per row. It needs no dollar signs because nothing is filled down, but it does not grow when you add rows unless you extend the ranges.

For groups inside groups, such as region and quarter, use SUMIFS with both conditions in the bottom of the fraction, so each row divides by the total for its own region and quarter.

A PivotTable can show the same percentages without formulas. Put Region and then Rep in Rows and Sales in Values. Right-click a value, choose Show Values As > % of Parent Row Total. Each rep then shows their share of their region, and each region row shows its share of the grand total. % of Parent Total does the same but lets you choose which field counts as the parent.

Which to pick: use the SUMIF column when the percentages sit next to your data and feed other formulas; use the PivotTable for a quick report from a long list that you refresh; use the spilled formula for a fixed-size list in Excel 365.

1. Click inside the data and choose Insert > PivotTable, then OK.
2. Drag Region to Rows, then Rep under it, and Sales to Values.
3. Right-click any number in the Sales column and choose Show Values As > % of Parent Row Total.
4. Drag Sales into Values a second time if you also want the amounts.