Md. Shamim Reza
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.
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.
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.
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)
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.
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.
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.
Similar Readings
- How to Sum Multiple Rows and Columns in Excel
- Excel Sum Last 5 Values in Row (Formula + VBA Code)
- How to Sum Only Positive Numbers in Excel (4 Simple Ways)
- Calculate Sum of Squares in Excel (6 Quick Tricks)
- Add Numbers in Excel (2 Easy Ways)
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.
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.
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 (“”).
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.
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.
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.
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]))
4. After that, right-click on the Index column and remove it.
5. Finally, from the ‘File’ menu, choose ‘Close & Load’.
6. At this time, you will get the cumulative sum as follows.
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
- How to Use VLOOKUP with SUM Function in Excel (6 Methods)
- Sum All Matches with VLOOKUP in Excel (3 Easy Ways)
- Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)
- How to Sum If Cell Contains Specific Text in Excel (6 Ways)
- 3 Easy Ways to Sum Top n Values in Excel
- How to Add Percentages to Numbers in Excel (4 Easy Ways)
