# How to Calculate Cumulative Sum in Excel (9 Methods)

Get FREE Advanced Excel Exercises with Solutions!

The Cumulative Sum is very commonly performed in Excel by many people all over the world. It is often called the Running Total/Balance. The cumulative sum is used in sales, bank statements, meal plans, and in many other fields. There are multiple ways to calculate cumulative sum in Excel. Here, in this article, I have explained 9 different ways for you to perform cumulative sum in Excel. The following picture shows the cumulative sum obtained by using one of the methods. ## 9 Ways to Perform Cumulative Sum in Excel

Here, I am going to show you 9 different ways to perform cumulative sum in excel. Let’s jump in!

### 1. Cumulative Sum Using the Addition Operator

Suppose you have a price list of grocery items and you want to calculate the cumulative sum in column D. You can do it by following the steps below.

Steps

1. At first, enter the following formula in cell D5:

`=C5` 2. It gives the same value in Cell D5 as in C5. 3. Secondly, enter the formula given below in cell D6:

`=D5+C6` 4. This will give the cumulative sum of the first two values. 5. Now, continue this formula to the entire column using the Fill Handle tool.

6. You will get the cumulative sum as follows. Note:

Remember, this method has one disadvantage. If any of the data rows gets deleted, it will give an error for the next rows. 7. To solve this problem, you have to copy the formula again to those cells in the same way above. Then it won’t show the error anymore. 8. Here is the final result with the FORMULATEXT function showing what happens in those D cells. ### 2. Cumulative Sum Using Excel SUM Function

Using the SUM function can be another way to solve the problem in the first method. Let’s execute the following steps one by one.

Steps

1. Firstly, enter the following formula in cell D5:

`=SUM(C5,D4)` 2. Then, apply this formula to the next cells by pulling the Fill-handle icon all the way.

3. It gives the same results as obtained from the first method earlier. ### 3. Cumulative Sum Using Absolute Reference with the SUM Function

Another way to obtain a cumulative sum is by using the SUM function and Absolute Reference.

Steps

1. First, enter the following formula in the cell D5:

`=SUM(\$C\$5:C5)`

2. It makes cell C5 an absolute reference and a relative reference at the same time. 3. Now, copying this formula to the other cells gives the desired result as shown below. ### 4. Calculate Running Total by Using SUM and INDEX Functions

Using the SUM and INDEX functions together can be an alternate way to perform cumulative sum in excel. For this, you have to have your data in the form of an excel table.

Steps

1. At first, enter the following formula in cell D5:

`=SUM(INDEX([Prices],1):[@prices])`

2. The first cell of the second column of the table is created as a reference by the INDEX function as 1 is the row_num argument. 3. Now, applying this formula to the entire column yields the following result. 4. Although this method is a little complex to understand, it works great for tabular data.

### 5. Perform Chain Summation Using an Excel Table

A similar way to the above method is to use the Header Cell Reference in an excel table.

Steps

1. At first, enter the following formula in cell D5:

`=SUM(Table5[[#Headers],[Prices]]:[@Prices])`

2. Don’t worry if this looks complicated. You can type the blue-colored part of the formula by clicking on cell C4. 3. Then, copy this formula to the next cells and get the result as follows. ### 6. Conditional Cumulative Sum Using the SUMIF Function

If you want to get the cumulative sum of special values, using the SUMIF function can be a smart way to do that. It uses absolute references as in method 3.

Consider the following data list where you want to get the cumulative sum only for the seafood-type items. Then, follow the steps below. Steps

1. First, enter the following formula in cell E5:

`=SUMIF(\$C\$5:C5,\$E\$4,\$D\$5:D5)` 2. Then, copy the formula down to the entire column.

3. You will see the resulting cumulative sum only recorded the prices for seafood-type items. ### 7. Cumulative Sum Using IF and SUM Functions Ignoring Text Value

Another way to perform cumulative sum with the condition of ignoring text values is the use of IF and SUM functions together. Consider the following data where some values are in the text format. So, if you want to perform a cumulative sum while ignoring those text values, follow the steps below.

Steps

1. Firstly, enter the following formula in cell D5:

`=IF(ISNUMBER(C5),SUM(\$C\$5:C5),"")`

2. The SUM function in this formula only accepts values from the cells of column C if the ISNUMBER function verifies it as a number. 3. Then, apply this formula down to all the other cells.

4. You will see some cells in the cumulative sum are empty. Because prices that correspond to those cells are not numbers. So the IF function gives an empty result as the given argument (“”). ### 8. Cumulative Sum Using the PivotTable

The cumulative sum can also be performed in excel using the PivotTable tool.

Before jumping in, click here to know how to insert a PivotTable.

Suppose you have a PivotTable as follows. Now follow the steps below to get a cumulative sum from this table.

Steps

1. Firstly, click anywhere in the PivotTable area. Now, in the PivotTable Fields, drag the Prices field in the Value area as shown in the following picture. 2. It will create another column named ‘Sum of Prices2’. 3. Then, click on the dropdown arrow and go to the Value Field Settings. 4. Now, change the custom name to ‘Cumulative Sum’ or as you wish.

5. Next, click on the ‘Show values as’ field.

6. After that, select ‘Running Total In’ from the dropdown list. Keep the ‘Base field’ as ‘Items’ and hit ‘OK’. 7. Finally, you will get the cumulative sum in a new column in your PivotTable. ### 9. Cumulative Sum Using the Power Query Tool

Another fascinating way to get the cumulative sum is by using the Power Query tool of Excel.

Consider, you have the following excel table on which you want to perform the cumulative sum using Power Query. Steps

1. At first, click anywhere on the excel table. Go to the Data tab and click on ‘From Table/Range’. It will open a table in the Power Query Editor. 2. Secondly, from the ‘Add Column’ tab, click on the small arrow right next to ‘Index Column’ which is a dropdown list, and choose ‘From 1’. Now, click on the ‘Custom Column’ icon. 3. After that, change the New Column Name in the ‘Custom Column’ dialog box to ‘Cumulative Sum’ or as you wish. Keep the ‘Items’ field selected in the ‘Available columns’ field. Then, type the following formula in the ‘Custom column formula’ field. Beware of any syntax errors. Now, hit the ‘OK’ button. This will generate a new column named ‘Cumulative Sum’.

`=List.Sum(List.Range(#"Added Index"[Prices],0,[Index]))` 4. After that, right-click on the Index column and remove it.  6. At this time, you will get the cumulative sum as follows. ## Things to Remember

• If you delete any data in between, the formula used in the first method will return #REF! error.
• Don’t forget to make sure that there is a “No syntax errors have been detected.” checkbox in step 3 of the last method.

## Conclusion

As the cumulative sum is a very popular operation, I have tried to explain 9 different methods to do it in excel for you. I have used the Addition Operator, the SUM, SUMIF, IF & SUM, and INDEX functions along with Absolute reference, Header cell reference, and Excel’s PivotTable and Power Query tools in those methods. If you have further queries, you can let us know in the comment section. Which method out of them do you prefer the most? Or, do you know any other ways to perform cumulative sum in excel?  Please share with us.

## Related Articles Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  