You can use the following formula to calculate a cumulative sum by group in Excel:
=SUMIF(A$2:A2,A2,B$2:B2)
This particular example assumes that the group values are in column A and the values to sum are in column B.
The following example shows how to use this formula in practice.
Example: Calculate Cumulative Sum by Group in Excel
Suppose we have the following dataset that shows the points scored by basketball players on various teams:
Suppose we would like to calculate the cumulative points for each team.
We can type the following formula into cell C2 to do so:
=SUMIF(A$2:A2,A2,B$2:B2)
We can then click and drag this formula down to each remaining cell in column C:
Column C displays the cumulative points values for each team.
For example:
- The cumulative points for Mavs is: 22, 36, 56
- The cumulative points for Warriors is: 17
- The cumulative points for Hawks is: 33, 53, 77
- The cumulative points for Kings is: 28, 53
- The cumulative points for Blazers is: 18, 31, 41
Notice that the values in the cumulative sum column restart for each new team name.
Note: This formula works even if each of the team names are not grouped together.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Calculate the Sum by Group in Excel
How to Calculate Relative Frequency in Excel
How to Calculate Cumulative Frequency in Excel
