Cumulative Sum in Excel If Condition Applied (6 Methods)

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.


Download Practice Workbook

You can download the workbook from the link below.


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 (9 Ways)


6 Methods to Calculate Cumulative Sum If Condition Applied

We will use the following dataset to explain the methods.

Excel Cumulative Sum if condition applied: Sample dataset

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.

Using IF with Cell Reference for Excel Cumulative Sum with Condition

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

IF with cell reference for 2nd cell in result and dragging results

πŸ”Ž 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 (5 Ways)


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.

Using Header Cell Reference for Excel Cumulative Sum with Condition

  • Select Table from the Insert tab.

Clicking Table from Insert tab

  • Check the range of the dataset and tick the My table has headers.
  • Then, click OK.

Checking table range and ticking headers

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

Excel table with arrow in headers

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

Result for header cell references

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.

Header cell reference with condition

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.

Filtering table for particular product ID

The result will look like this:

The result for particular repetitive product ID

πŸ”Ž 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 formula 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 Ways)


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

Furthermore, we can use the nested formula of the IF and SUM function 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.

Using Nested IF and SUM for Excel Cumulative Sum with Condition

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.

Final result for SUM and IF formula

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.

Using SUM and INDEX for Excel Cumulative Sum If Condition Applied

  • Choose Table from the Insert tab.

Using table from Insert tab for SUM and INDEX formula application

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

Checking table range and ticking headers for application of SUM and INDEX formula

The table will have headers with arrows.

Table with headers for application of SUM and INDEX formula

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

Result of SUM and INDEX formula

Notice the results for the repetitive values.

Observation of the result for SUM and INDEX formula

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.

Filtering for repetitive cell in case of SUM and INDEX formula result

Observe that the result is the same.

Result of filtering using SUM and INDEX

πŸ”Ž 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.

Using SUMIF for Excel Cumulative Sum with Condition

You can see the results only for the repetitive data.

Observation of the result of using SUMIF formula

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.

Using SUMIFS for Excel Cumulative Sum with Condition

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.

Selecting the modified dataset for pivot table

  • Select Pivot Table from the Insert tab.

Selecting Pivot table from 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.

Checking range and selecting location of pivot table

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.

Pivot table box

Options for pivot table

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

Selecting options to create pivot table

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

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

Creating cumulative sum column in pivot table

The result will show up as follows.

Result of pivot table

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

Read More: How to Use Pivot Table to Calculate Running Total by Date in Excel


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.


Related Articles

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo