About this exercise
A 250-row dataset on one sheet, 3 answer cells, about 7 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- G2
=SUMIFS($D$2:$D$251,$C$2:$C$251,F2) - G7
=SUM(G2:G6) - H2
=G2/$G$7
A PivotTable's Show Values As → % of Grand Total divides every value by the grand total of the report. The formula version does the same in two steps: SUMIFS rebuilds the Sum of Revenue per channel, and =G2/$G$7 divides each one by the locked grand total, so the Grand Total row itself comes out as 100%. The 0.0% format only changes the display (0.123 shows as 12.3%); one decimal keeps a tiny channel such as Phone orders from reading 0%.
The PivotTable route in Excel: drag Revenue to Values twice, right-click a value of the second copy → Show Values As → % of Grand Total, then rename the field (for example "% of Grand Total") and set its number format through Value Field Settings → Number Format. Pitfall 1: a PivotTable calculates the shares over the items that are visible — filter one channel out and every other share is recalculated over the rest; the formula version keeps dividing by the total in G7 unless you change that cell. Pitfall 2: =G2/G7 filled down without $ divides H3 by G8, an empty cell, and shows #DIV/0!. Pitfall 3: rounded shares need not add up to exactly 100.0% on screen (99.9% or 100.1% is normal); the stored values still sum to 1. Never multiply by 100 in the formula and then apply % — 0.123 × 100 would show as 1230.0%. With no field in Columns, % of Column Total gives the same numbers as % of Grand Total. In Excel 365, =PERCENTOF(G2,$G$2:$G$6) is the same ratio, and =GROUPBY(C2:C251,D2:D251,PERCENTOF) spills the channel labels with their shares and a Total of 100% in one formula.
Other ways that work
- =SUMIF($C$2:$C$251,F2,$D$2:$D$251) in G2 — SUMIF with one condition (criteria range, criterion, sum range)
- =G2/SUM($G$2:$G$6) in H2 — divides by the channel total directly (then H7 = G7/SUM($G$2:$G$6) is still 100%)
- =SUMIFS($D$2:$D$251,$C$2:$C$251,F2)/SUM($D$2:$D$251) in H2:H6 — revenue and share in one step; the Grand Total row then needs its own =SUM(H2:H6)
- =PERCENTOF(G2,$G$2:$G$6) in H2 — Excel 365 only
Common mistakes
Filling =G2/G7 down without the $: H3 then divides by G8, an empty cell, and shows #DIV/0!; lock the grand total as $G$7.
How we grade
Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.
Answers computed with Excel 365 rules and checked in our grading engine on November 20, 2026. Author: GridBelt team.