How to Calculate Cumulative Sum in Excel (9 Methods)

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

Cumulative Sum using SUM Function in Excel

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.

dataset to perform cumulative sum using addition operator

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

entering formula for cumulative sum using addition operator

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.

result for cumulative sum using addition operator

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

error in cumulative sum calculation

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

Here is the final result with the FORMULATEXT function showing what happens in those D cells.

formulatext for cumulative sum using addition operator

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)

entering formula for cumulative sum using sum function

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.

formula for cumulative sum using absolute reference

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.

entering formula for cumulative sum using SUM and INDEX functions

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.

entering formula for cumulative sum using header cell reference in excel table

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.

dataset for conditional cumulative sum

Steps

Enter the following formula in cell E5:

=SUMIF($C$5:C5,$E$4,$D$5:D5)

entering formula for conditional cumulative sum

Copy the formula down to the entire column.

result for conditional cumulative sum

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

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

dataset with text values to perform cumulative sum

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.

result showing text values ignored in cumulative sum

Method 8 – Cumulative Sum Using a Pivot Table

Suppose you have a PivotTable as follows.

a PivotTable to perform cumulative sum

Steps

Click here to learn how to insert a PivotTable.
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.

result for cumulative sum using PivotTable in Excel

Read More: Shortcut for Sum in Excel

Method 9 – Cumulative Sum Using the Power Query Tool

Consider the following Excel table.

data table for cumulative sum using Power Query in Excel

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]))