running total in excel based on criteria

Calculating Running Totals in Excel Based on Criteria

By Emily Lee
AD

Running a total in Excel based on specific criteria is a powerful feature that enables you to summarize and analyze data effectively. This can be achieved using SUMIF, SUMIFS, or other similar functions. Let's explore how to calculate a running total in Excel based on certain conditions.

Before we dive into the formulas, let's assume you have a dataset in Column A (starting from A2) with different sales amounts, and you want to find the running total based on a specific condition, e.g., 'East' in Column B (starting from B2).

Understanding SUMIF and SUMIFS

Before using these functions to create a running total, it's crucial to understand how they work. Both SUMIF and SUMIFS allow you to add up cells based on specific criteria. The primary difference is that SUMIFS can apply multiple criteria, while SUMIF is limited to one.

AD
Running Total in Excel
Running Total in Excel
How to Calculate Running Total in Excel? (Complete Guide) - ExcelDemy
How to Calculate Running Total in Excel? (Complete Guide) - ExcelDemy
The Ultimate Guide to Running Total Formula in Excel for Instant Results
The Ultimate Guide to Running Total Formula in Excel for Instant Results

Now, let's discuss the steps to create a running total in Excel based on criteria.

Using SUMIF with a single criterion

The SUMIF function adds up cells based on a specified criterion. The syntax is: SUMIF(range, criterion, [sum_range]). In our example, we'll use the following formula in cell C2:

Running Total In Excel
Running Total In Excel
running total formula Excel
running total formula Excel
How to Calculate Horizontal Running Total in Excel (3 Easy Ways) - ExcelDemy
How to Calculate Horizontal Running Total in Excel (3 Easy Ways) - ExcelDemy

=SUMIF($A$2:A2, B2)

This formula calculates the running total of sales ('East' in this case) up to the current row. To extend this formula to the rest of the data, simply drag it down to copy the result for each criterion in Column B.

AD

Using SUMIFS with multiple criteria

If you need to apply more than one condition to calculate the running total, use the SUMIFS function. Its syntax is: SUMIFS(sum_range, criterion_range1, criterion1, [criterion_range2, criterion2], ...). Suppose you want to add a new criterion, 'January,' in Column C (starting from C2). Then, update the formula in cell C2 as follows:

How to Calculate Running Total in Google Sheets
How to Calculate Running Total in Google Sheets
How to Calculate Running Total in Excel (Step-by-Step)
How to Calculate Running Total in Excel (Step-by-Step)
Excel Table Running Total Formula • My Online Training Hub
Excel Table Running Total Formula • My Online Training Hub
Use Running Total to Compare Years in Excel - Contextures Blog
Use Running Total to Compare Years in Excel - Contextures Blog
Power BI Running Total
Power BI Running Total
Sirexcelco - Etsy
Sirexcelco - Etsy

=SUMIFS($A$2:A2, $B$2:B2, B2, $C$2:C2, C2)

Now, the running total is calculated based on both the region and the month. Drag this formula down to apply it to the entire dataset.

AD

Creating a running total withagog trailed zeros

When calculating running totals, it's common to have blank cells due to missing criteria. To improve the appearance of your data, you can add trailed zeros to these cells using conditional formatting. Here's how:

How To Calculate Running Total in Google Sheets
How To Calculate Running Total in Google Sheets

Applying conditional formatting with formula

Select the range containing your running totals (e.g., C2 to the last non-empty cell). Then, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. In the box, enter the following formula:

=andosien(A2,"0")

AD

This formula checks if the value in cell A2 contains any zeros. If it does, the cells are formatted with trailed zeros. Select the desired formatting (e.g., fill color) and click 'OK.'

That's it! Now you know how to calculate a running total in Excel based on specific criteria and how to apply trailed zeros for better data presentation.

Try experimenting with different criteria and ranges to master this powerful feature. Practice makes perfect, so keep playing around with your Excel sheets. Happy calculating!

AD