When analyzing data, it’s important not only to sum the values, but to understand what share each row takes in the total on a running basis. This shows the contribution of each element and is used in sales analysis, ABC analysis and Pareto charts.
Example: revenue distribution
The source table:
| Product | Revenue |
|---|---|
| A | 400 |
| B | 300 |
| C | 200 |
| D | 100 |
The total is 1,000. The running total adds each value to the sum of the previous ones, and the cumulative share divides it by the total:
| Product | Revenue | Running total | Cumulative share |
|---|---|---|---|
| A | 400 | 400 | 40% |
| B | 300 | 700 | 70% |
| C | 200 | 900 | 90% |
| D | 100 | 1000 | 100% |
How to calculate it with formulas
With the revenue in B2:B5, the running total in C2, filled down:
=SUM($B$2:B2)
Only the start of the range is fixed with dollar signs, so in each next row it includes one more value. The cumulative share in D2:
=SUM($B$2:B2)/SUM($B$2:$B$5)
Format the result as a percentage. In Excel 365, the whole column can be calculated with one formula:
=SCAN(0,B2:B5,LAMBDA(acc,x,acc+x))/SUM(B2:B5)
Usually you should sort the values in descending order first, otherwise the result is hard to interpret. If you don’t want to sort, this formula gives each row the cumulative share it would have in a sorted list:
=SUMIF($B$2:$B$5,">="&B2)/SUM($B$2:$B$5)
It adds all values greater than or equal to the current one. Equal values get the same share.
Automating it with !SEMTools
With the !SEMTools add-in, you get the cumulative share at once, without the intermediate running total:
- select the range of values;
- go to the !SEMTools tab;
- open the Extract → Count menu;
- click Cumulative share.
If the “output to the next column” checkbox is on, the cumulative share appears to the right of the source column; otherwise, the values are replaced in place. The second option is handy too if you work on a copy of the data. The add-in returns values only, no formulas.
Where the cumulative share is used
- sales analysis: which products bring 80% of revenue;
- traffic analysis: which pages bring most of the visits;
- financial reports: distribution by clients, regions, categories;
- ABC analysis: finding the key elements by the Pareto principle.
Tips
- sort the values in descending order before calculating, or use the SUMIF formula above;
- for a chart, a combo chart works best: columns for the values and a line for the cumulative share on a secondary axis;
- if you do this analysis often, automation saves a lot of time.
Get the cumulative share of a whole column in one click, in place or into the next column, without running-total formulas.
!SEMTools solves this and hundreds of other tasks right in Excel!
Summary
I hope you found what you were looking for on this page. The cumulative share is the running total divided by the total: SUM with a growing range in any Excel, SCAN in Excel 365, or one click in !SEMTools. Other metrics are in the statistical metrics section.