Cumulative summation is one of the most used operations throughout the world to keep track of data related to similar things. Cumulative summation helps to see the total of each of the values within it. There are some methods through which we can do cumulative summation in Excel. However, it also provides various methods for cumulative sum if the condition is applied to it. This article will explain 6 methods for cumulative sum if the condition is applied in Excel.

**Table of Contents**hide

**What is the Cumulative Total?**

A cumulative total is a process of observing running summation results every time the dataset is updated with new data. This helps to get an idea or keep a record of the increment or decrement of stock or relevant things in the corporate world.

Excel has some extremely useful methods of doing this with ease. We will be seeing the methods in this article.

**Read More:**How to Calculate Running Total in Excel

**Excel Cumulative Sum If Condition: 6 Methods to Calculate**

We will use the following dataset to explain the methods.

The dataset contains the **Products ID** and **Sales** column. We might want to have the **cumulative total** of the sales for a particular product ID. Let us discuss and learn how to do this with 6 different methods.

### 1. Using IF with Cell Reference for Excel Cumulative Sum with Condition

The simplest method is using **the IF function** and **Cell References**. Follow the steps below to apply **IF** and cell references for cumulative total with the condition.

**Condition:** We will do the cumulative summation only if the sales value is less than **5000**.

**Steps:**

- In Cell
**D5**, write the formula for the first cell of the sales column in the dataset:

`=IF(C5<F5,C5,"")`

- Press
**Enter.**

- After that, write the formula given below in
**Cell D6**:

`=IF(C6<$F$5,D5+C6,"")`

- Again, press
**Enter.** - Drag the result using the
**Fill handle**till the end of the table.

**ðŸ”Ž**** How Do the Formulas Work?**

ðŸ“Œ In the first cell of the result, we used the **IF** formula, where the logic is the values of sales less than 5000.

ðŸ“Œ Within the **IF** formula the return for true is the same as the sales gave otherwise blank.

ðŸ“Œ For the second cell of the result, we put the same logic and false return value. The only change is the return for the true value which will do a summation of the current sales value with the result of the previous cell.

*Note:** We can find the condition of 5000 in cell F5 and mention this in the formula as an absolute reference.*

**Read More: **How to Calculate Running Total in One Cell in Excel

### 2. Using Header Cell Reference for Excel Cumulative Sum with Condition

Moreover, Suppose, this time our sample dataset has some repetitions, and we want to calculate the cumulative sum for repetitive values. For this, we can use a header cell reference for excel cumulative sum based on condition.

**Condition:Â **Cumulative sum for repetitive values.

**Steps:**

- Select the modified dataset.

- Select
**Table**from the**Insert**tab.

- Check the range of the dataset and tick the
**My table has header**s. - Then, click
**OK**.

This will create a table with headers having an arrow sign for each as shown below.

- Now, take a new column. In
**Cell D5**write the following formula:

`=SUM(Table1[[#Headers],[Sales]]:[@Sales])`

- Press
**Enter**and then drag the result till the end.

This was the result of the cumulative sum without any condition and so it normally showed results for all the values.

- Now if we apply the condition that it will show the cumulative sum for repetitive values only. The formula for this:

`=SUMIF(Table1[[#Headers],[Product]]:[@Product],[@Product],Table1[[#Headers],[Sales]]:[@Sales])`

Write this formula in **Cell E5**.

- Afterward, press
**Enter**and drag the result using the**fill handle**to the end of the table.

This will show the result of the cumulative sum for repetitive values only.

To see the result of the cumulative sum you need to follow the steps:

**Right-click**on the arrow with the**Product**header and it will open a**drop-down menu**.**Select**the product for which you want to see the result.- Click
**OK**.

The result will look like this:

**ðŸ”Ž**** How Do the Formulas Work?**

ðŸ“Œ Creating the Excel table helped to directly click on headers and cells to refer to the cells without the hassle of using cell references. It is called table references.

ðŸ“Œ As **the SUMIF** **function** takes arguments of sum_range which is the sales column in our case.

ðŸ“Œ The other arguments include criteria range and criteria which are from the product column of the dataset.

ðŸ“Œ The first **SUMIF** formula gave cumulative summation for all the data while the second one for the condition that only repetitive values will have the cumulative sum.

**Read More: **How to Calculate Horizontal Running Total in Excel

### 3. Using Nested IF and SUM for Excel Cumulative Sum with Condition

Furthermore, we can use the nested formula of the **IF** and** SUM** functions to get the cumulative sum for the condition applied.

**Condition:Â **Keep the cumulative total cells blank for blank data.

First, let us see the result using the **SUM** formula only.

**Steps:**

- Write the
**SUM**formula in**Cell D5**:

`=SUM($C$5:C5)`

- Then you need to press
**Enter**to get the result and drag the result.

The result shows if we drag after the table data, it will show the last result.

- Now, if we use the
**IF**formula to keep blank for the results of blank data. You should write the formula in**C****ell E5**:

`=IF(C5="","",SUM($C$5:C5))`

- You have to press Enter and drag the result up to where you want.

Notice that there are no constant results in blank cells.

**ðŸ”Ž**** How Do the Formulas Work?**

ðŸ“Œ The **SUM** formula uses cell references to give the result of summation.

ðŸ“Œ It gives a constant value for blank cells.

ðŸ“ŒUsing the** IF** formula shows blank results for blank cells and cumulative sum for others.

### 4. Using SUM and INDEX for Excel Cumulative Sum If Condition Applied

There are other formulas like the nested formulas using** SUM** and **INDEX**.

**Condition:** Cumulative total for a certain item from the product list.

**Steps:**

- Select the dataset.

- Choose
**Table**from the**Insert**tab.

- Check the table range and tick
**My table has headers**option. - Then, click
**OK**.

The table will have headers with arrows.

- Followingly, you have to write the following formula in
**Cell D5**:

`=SUM(INDEX(Table2[[#Headers],[Sales]],1):Table2[@Sales])`

- Press
**Enter**and drag the result till the end of the table.

Notice the results for the repetitive values.

This does not consider and give results sequentially.

- If you right-click on the arrow beside the header of Product and select any of the repetitive products. Then click
**OK**.

Observe that the result is the same.

**ðŸ”Ž**** How Do the Formulas Work?**

ðŸ“Œ The **INDEX** formula uses row and column numbers to find a reference in a range.

ðŸ“Œ After that, it uses the **SUM** formula to give the summation as result.

### 5. Using SUMIF/SUMIFS for Excel Cumulative Sum with Condition

To solve the issues with the above method we can use the **SUMIF **or **SUMIFS **formula for condition-based cumulative sum.

**5.1 Using SUMIF**

**Condition:** Cumulative total for a certain product that repeats multiple times.

Follow the steps below to apply **SUMIF** for this kind of cumulative sum.

**Steps**:

- Firstly, write the following formula in
**Cell D5**.

`=SUMIF(Table35[[#Headers],[Product]]:Table35[@Product],Table35[@Product],Table35[[#Headers],[Sales]]:Table35[@Sales])`

- Press
**Enter**and drag the results below.

You can see the results only for the repetitive data.

**5.2 Using SUMIFS**

**Condition:** Cumulative total for certain products that repeat multiple times.

Similarly, for applying **SUMIFS** follow the steps:

**Steps:**

- Write the formula in
**cell E5**:

`=SUMIFS(Table35[[#Headers],[Sales]]:Table35[@Sales],Table35[[#Headers],[Product]]:Table35[@Product],Table35[@Product])`

- Press
**Enter**and drag it using the**fill handle**.

This gives results alike the** SUMIF** formula.

### 6. Using Pivot Table for Excel Cumulative Sum If Condition Applied

**Condition:Â **Cumulative sum for unique values.

The pivot table is an amazing tool to find the cumulative sum if the condition is applied.

Follow the steps to apply this:

**Steps:**

- Select the modified dataset.

- Select
**Pivot Table**from the**Insert**tab.

- A new box will open where:
- Check the
**table range**. - Select
**Existing Worksheet**. - Select a cell beside the dataset in the
**Location:**section. - Thereafter, click
**OK**.

- Check the

This will open two boxes one at the selected location and the other at the right side of the sheet. The boxes will look like this.

- Tick the Products and Sales. This will show them in the
**Rows**and**Values**section as shown with arrows in the picture below.

- Select the drop-down from the
**Sum of Sales**and click**Value Field Settings**.

- This will open a new box. There-
- Write
**Cumulative Sum**in**Custom Name:**section. - Select
**Show Values As**. - Choose
**Running Total**from the**Show values as**a drop-down menu. - Then, select
**Product**. - Lastly, click
**OK**.

- Write

The result will show up as follows.

Here, you can see that it adds up the repetitive values and shows only the cumulative sum for the unique product ID.

**Download Practice Workbook**

You can download the workbook from the link below.

**Conclusion**

The article explains 6 different methods for Excel cumulative sum if a condition is applied. Sequentially, the methods include Excel formulas using the **SUM**, **SUMIF**, **IF**, **INDEX**, and so on functions, and Excel tools like **Pivot Table**, **Excel Table**, etc. I hope the article was helpful to you. If you have any queries, you can write them up in the comment section.