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.
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 headers.
- 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 Cell 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.
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.
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.
