Each product's share of revenue (SUMIF over a locked total)
The problem
Problem statement
Revenue is logged per order in A2:B5, so a product appears on several rows. Put the share of total revenue earned by the product on row 2 in the Share cell E2, as a decimal.
The denominator is the whole book, so it must not move when the formula is reused for another product.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | |
|---|---|---|---|---|
| 1 | Product | Revenue | Product | Share |
| 2 | Widget | 4000 | ||
| 3 | Widget | 2000 | ||
| 4 | Bolt | 8000 | ||
| 5 | Nut | 6000 |
Notes
This needs two different aggregates: the product's own total (SUMIF, because it spans several rows) and the grand total (SUM, locked with $). Using the row's revenue instead of the product's total gives the share of one order, not of the product.
How to approach
Your answer goes in E2.
- Target cell is E2
- The numerator is the product total, not B2
- The denominator is the whole column total and must be locked
- Shape: =SUMIF(?, ?, ?) / SUM(?)
Function guide
FAQ
How do I calculate each product's share of revenue?
=SUMIF(products, product, revenue) / SUM(revenue), with the denominator locked ($) so it does not move when filled down.
Why do my shares not add up to 100%?
The denominator is probably sliding, or one product's total is being taken from a single row. Sum by product first, then divide by the grand total.