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

The following picture shows the cumulative sum obtained by using one of the methods.

## 9 Ways to Perform Cumulative Sum in Excel

### Method 1 – Cumulative Sum Using the Addition Operator

We have a price list of grocery items and want to calculate the cumulative sum in column D.

Steps

• Enter the following formula in cell D5:
`=C5`

• It gives the same value in Cell D5 as in C5.

• Enter the formula given below in cell D6:
`=D5+C6`

• This will give the cumulative sum of the first two values.

• Continue this formula to the entire column using the Fill Handle tool.
• You will get the cumulative sum as follows.

Note: If any of the data rows gets deleted, you will get an error for the next rows.

• If a row is deleted, copy the formula again to those cells.

Read More: Sum Formula Shortcuts in ExcelÂ

### Method 2 – Cumulative Sum Using the Excel SUM Function

Steps

• Enter the following formula in cell D5:
`=SUM(C5,D4)`

• Apply this formula to the next cells by pulling the Fill-handle icon all the way.

### Method 3 – Cumulative Sum Using an Absolute Reference with the SUM Function

Steps

• Enter the following formula in the cell D5:
`=SUM(\$C\$5:C5)`

It makes cell C5 an absolute reference as the starting point.

• Copying this formula to the other cells gives the desired result as shown below.

### Method 4 – Calculate the Running Total by Using SUM and INDEX Functions

Steps

• Convert the range into a table by pressing Ctrl + T.
• Enter the following formula in cell D5:
`=SUM(INDEX([Prices],1):[@prices])`

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.

• 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.

### Method 5 – Perform Chain Summation Using an Excel Table

Steps

• Convert the dataset into a table.
• Enter the following formula in cell D5:
`=SUM(Table5[[#Headers],[Prices]]:[@Prices])`

You can type the blue-colored part of the formula by clicking on cell C4.

• Copy this formula to the next cells and get the result as follows.

### Method 6 – Conditional Cumulative Sum Using the SUMIF Function

We want to get the cumulative sum only for the seafood items.

Steps

• Enter the following formula in cell E5:
`=SUMIF(\$C\$5:C5,\$E\$4,\$D\$5:D5)`

• Copy the formula down to the entire column.

### Method 7 – Cumulative Sum Using IF and SUM Functions Ignoring Text Values

Consider the following data where some values are in the text format.

Steps

• Enter the following formula in cell D5:
`=IF(ISNUMBER(C5),SUM(\$C\$5:C5),"")`

The SUM function in this formula only accepts values from the cells of column C if the ISNUMBER function verifies it as a number.

• Apply this formula down to all the other cells.

### Method 8 – Cumulative Sum Using a Pivot Table

Suppose you have a PivotTable as follows.

Steps

• Click anywhere in the PivotTable area.
• In the PivotTable Fields, drag the Prices field in the Value area as shown in the following picture.

• It will create another column named â€˜Sum of Prices2â€™.

• Click on the dropdown arrow and go to the Value Field Settings.

• Change the custom name to Cumulative SumÂ or as you wish.
• Click on the Show values asÂ field.
• Select Running Total InÂ from the dropdown list.
• Keep the Base field as Items and hit OK.

• You will get the cumulative sum in a new column in your Pivot Table.

Read More: Shortcut for Sum in ExcelÂ

### Method 9 – Cumulative Sum Using the Power Query Tool

Consider the following Excel table.

Steps

• Go to the Data tab and click on From Table/Range.
• It will open a table in the Power Query Editor.

• From the Add Column tab, click on the small arrow right next to Index Column and choose From 1.
• Click on the Custom Column icon.

• Change the New Column Name in the Custom ColumnÂ dialog box to Cumulative Sum.
• Keep the â€˜Itemsâ€™ field selected in the Available columnsÂ field.
• Use the following formula in the Custom column formula field.
`=List.Sum(List.Range(#"Added Index"[Prices],0,[Index]))`
• Hit the OK button. This will generate a new column named Cumulative Sum.

• Right-click on the Index column and remove it.

• You will get the cumulative sum as follows.

Read More: Excel Sum If a Cell Contains CriteriaÂ

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF