To calculate the cumulative sum in Excel, you need to use the SUM formula. You need to refer to the previous values and the current value in the formula and then drag that formula to all the cells up to which you want to calculate the running total (cumulative sum).
In this tutorial, we will learn to write this formula and a few other formulas to deal with different situations.
Formula to Calculate a Cumulative SUM
You can use the following steps:
- First, enter the sum function in cell C2.
- After that, refer to the range B2:B3. This range includes the quantity for day 1 and day 2.
- Now, use the dollar sign before the row number of the cell reference.
- Next, enter the function to get the cumulative total for two days (1 and 2).
- In the end, drag the formula up to the last cell in the column to get the running total for all the days.
You can see when you use the dollar sign, it freezes the first cell and, in each cell, calculate the cumulative sum for all the previous days.
Cumulative Sum by Date
If you have data with dates as we have in column A.
To get the date-wise running total, first, you need to sort data date wise old to new.
After that, write the formula, in the same way as we have done in the previous example.
And in the end, drag this formula up to the last cell with the date.
Cumulative Sum by Month
In the following example, we have dates in column A and we want to calculate the running total based on the months instead of the dates. For this, the first thing you need to do is to add a separate column with month names.
Now in the next column, enter the following formula by referring to the date and quantity column from the original table.
=SUMPRODUCT(--(TEXT($A$2:$A$11,"MMM")=D2),$B$2:$B$11)
This formula will give you a month-wise total for each month. And for the months for which you don’t have any date will have 0. The next thing from here is to add a column for the running total (cumulative sum).
Use the same way, enter the sum function, refer to the cells E2 and E3, and freeze the row for the cell E2 by using the dollar sign before the row number.
After that, drag the formula, up to the last cell month.