Ada's share of total revenue (locked denominator)
The problem
Problem statement
The team lead wants each rep's slice of the month: Ada's revenue divided by the team total, as a decimal (0.08 means 8%). Revenue is in B2:B6 and Ada is the first row. Put her share in the Share of total cell D2.
The denominator must stay the whole team when the formula is reused for other reps — lock it before you fill down.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | |
|---|---|---|---|
| 1 | Rep | Amount | Share of total |
| 2 | Ada | 120 | |
| 3 | Ben | 480 | |
| 4 | Cleo | 300 | |
| 5 | Dana | 200 | |
| 6 | Eli | 400 |
Notes
A share is part / whole. The numerator moves with the row (B2), the denominator must not ($B$2:$B$6) — that is the entire lesson of absolute references, and the reason a plain B2/SUM(B2:B6) breaks the moment it is filled down.
How to approach
Your answer goes in D2. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is D2
- Numerator is B2 — it should move when you fill down
- Denominator is SUM(B2:B6), and it must not move
- Shape: =B2/SUM(?) — how do you lock a range?
Official solutions
Show 2 solutions — try it yourself first
Approach 1: divide by a locked SUM
=B2/SUM($B$2:$B$6)
120 / 1500 = 0.08. The $ signs pin the total range, so filling the formula down keeps the denominator on B2:B6 while the numerator follows the row.
Approach 2: SUMIF so the formula finds its own row
=SUMIF($A$2:$A$6, A2, $B$2:$B$6)/SUM($B$2:$B$6)
SUMIF looks Ada up by name instead of relying on the row position, then divides by the same locked total. Useful when rows are sorted or filtered and position is no longer trustworthy.
Function guide
FAQ
Why does my percentage formula break when I fill it down?
The denominator is sliding with the row. Lock it with $: SUM($B$2:$B$6). One shared total, one moving numerator.
Should the answer be 0.08 or 8%?
Same number, different display. Type 0.08 and format the cell as a percentage if you want to see 8%. Storing the decimal keeps it usable in further arithmetic.