Table of Contents
A conditional running total in Excel is a running total that can be calculated using a specific criteria such as a specific date, value, or other criteria. To calculate such a running total, the SUMIF and OFFSET functions can be used. The SUMIF function can be used to add up all values that meet a certain criteria, while OFFSET can be used to limit the range of values that are used in the SUMIF calculation. With these functions, a conditional running total in Excel can be easily calculated.
Often you may want to calculate a running total of values in a column in Excel that is conditional on the values in some other column.
The following example shows how to do so.
Example: Conditional Running Total in Excel
Suppose we have the following dataset in Excel that shows the total sales made by some company on various days:
Suppose we’d like to calculate a running total of sales that restarts on each new day.
First, we can enter the first value of sales in column C:
Next, we can use the following formula to calculate a running total of sales that restarts on each new day:
=IF(A3=A2, C2+B3, B3)
This formula checks if the current date in column A is equal to the date in the previous row.
If the dates match, then the sales from the current row is added to the sales from the previous row.
If the dates don’t match, then the sales from the current row is used as the new starting amount for sales.
The following screenshot shows how to use this formula in practice:
Column C shows the running total of sales for each day.
Notice that the running total of sales in column C restarts for each new day in column A.