Formula Errors

How to Create a Conditional Running Total That Resets at 9 in Excel

Steve K Oct 9, 2026 869 views

Question details

The user needs a row-by-row formula in Excel to calculate a running total in one column by accumulating values from an adjacent column, which automatically resets whenever a specific condition (such as the number 9) appears.

How to Create a Conditional Running Total That Resets at 9 in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating an accumulative sum that automatically restarts based on specific trigger values in another column, adjusting for complex group resets.
Observed behavior: The formula successfully maintains a running total by accumulating values continuously until the condition (e.g., a 9 in column A) is met, at which point the total resets and begins a new group calculation.

Before you start

Ensure your data is organized in continuous columns without blank rows separating your groups, and identify exactly which column contains the trigger value for the reset.

Solution 1Recommended

Use the IF Function for a Basic Reset Running Total

This is the most straightforward method to calculate a running total that resets when a specific value appears in an adjacent column.

By referencing the previous row's total and adding the current row's value, you can create a dynamic running total. The IF function evaluates whether the current row contains the reset trigger (e.g., 9).

Select the starting cell

Click on the cell where you want your first calculated total to appear, typically C2, assuming your headers are in row 1.

Enter the IF formula

Type the formula =IF(A2=9, B2, C1+B2) into the formula bar. This checks if column A contains 9; if it does, it starts a new total with the value in B2. Otherwise, it adds B2 to the previous total in C1.

Apply the calculation

Press Enter to finalize the formula for the first row.

Fill the formula downward

Click and hold the small green square (fill handle) in the bottom-right corner of cell C2, and drag it downward to apply the logic to the rest of your data.

Use the IF Function for a Basic Reset Running Total

Handling Text Triggers: If your reset trigger is a word instead of a number, ensure you enclose the text in double quotes within the formula, such as =IF(A2="Reset", B2, C1+B2).

Solution 2

Create a Complex Reset Logic Using Nested IFs and AND

Use this method if your counting should only begin or reset when multiple conditions are met simultaneously.

Smart Data Analysis

Calculate Conditional Running Totals Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical formulas like IF and AND, making it incredibly easy to create complex running totals and dynamic data models. You can perform these calculations seamlessly with an intuitive interface.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing the data you want to summarize.
2. Select the target cell: Click on the cell where the running total should begin (for instance, cell C2).
3. Input the formula: Type the conditional formula, such as =IF(A2=9, B2, C1+B2), into the formula bar.
4. Apply to the entire column: Press Enter, then simply double-click the fill handle at the bottom right of the cell to instantly apply the running total down the entire column.

Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Lightweight application that runs smoothly without lagging on large datasets.Intuitive UI that makes writing and dragging formulas quick and error-free.Free to use for everyday data analysis and financial calculations.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #VALUE! error when dragging my running total formula down?

This error typically occurs if the formula attempts to add a numeric value to a text string. Ensure that the cell above your starting formula (e.g., C1) contains a number or is empty, rather than containing a text header. You might need to manually set the first row's total to just =B2 to avoid referencing a text header.

Can I reset the running total based on an empty cell instead of a specific number?

Yes, you can modify the IF function to check for blanks. Use a formula like =IF(A2="", B2, C1+B2) to restart the calculation whenever there is an empty cell in column A.

How do I make the running total reset daily using a date column?

To reset the total when the date changes, compare the current row's date with the previous row's date. Use a formula like =IF(A2<>A1, B2, C1+B2), assuming column A contains your dates.

Excel & Data Problems