How to Calculate Cumulative Sum in Excel (9 Methods)

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.

Cumulative Sum using SUM Function in Excel


Download Practice Workbook

You can download the practice workbook from here.


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.

dataset to perform cumulative sum using addition operator

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

entering formula for cumulative sum using addition operator

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.

result for cumulative sum using addition operator

Note:

Remember, this method has one disadvantage. If any of the data rows gets deleted, it will give an error for the next rows.

error in cumulative sum calculation

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.

formulatext for cumulative sum using addition operator

Read More: Sum Formula Shortcuts in Excel (3 Quick Ways)


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)

entering formula for cumulative sum using sum function

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.

Read More: How to Sum Between Two Numbers Formula in Excel


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.

formula for cumulative sum using absolute reference

3. Now, copying this formula to the other cells gives the desired result as shown below.

Read More: [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)


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.

entering formula for cumulative sum using SUM and INDEX functions

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.

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

3. Then, copy this formula to the next cells and get the result as follows.


Similar Readings


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.

dataset for conditional cumulative sum

Steps

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

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

entering formula for conditional cumulative sum

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.

result for conditional cumulative sum

Read More: Sum Cells in Excel: Continuous, Random, With Criteria, etc.


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.

dataset with text values to perform cumulative sum

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 (“”).

result showing text values ignored in cumulative sum

Read More: How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)


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.

a PivotTable to perform cumulative sum

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.

result for cumulative sum using PivotTable in Excel

Read More: Shortcut for Sum in Excel (2 Quick Tricks)


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.

data table for cumulative sum using Power Query in Excel

Then, follow the steps below.

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

entering formula in Power Query Editor for cumulative sum

4. After that, right-click on the Index column and remove it.

removing index column in Power Query Editor table for cumulative sum

5. Finally, from the ‘File’ menu, choose ‘Close & Load’.

closing Power Query Editor to get the cumulative sum results

6. At this time, you will get the cumulative sum as follows.

generated result for cumulative sum by Power Query in Excel

Read More: Excel Sum If a Cell Contains Criteria (5 Examples)


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

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

Leave a reply

ExcelDemy
Logo