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.

**Table of Contents**Expand

**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

**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

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

**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

**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

**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

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

**Download Practice Workbook**

You can download the practice workbook from here.

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