Calculation Issues

How to Create an Excel Running Total That Resets When Another Column is Zero

Maira Mehtab Sep 24, 2026 869 views

Question details

The user wants to create a conditional running total in column C that sums values from column A, but automatically resets to start over whenever the corresponding value in column B is zero.

Product: Excel
Device & OS: not provided
Scenario: Tracking continuous numeric data (such as inventory, finances, or scores) where specific conditions trigger a new calculation cycle.
Observed behavior: Need a reliable formula to identify the zero reset marker in column B and dynamically restart the sum of column A.

Before you start

Ensure your data in Column A contains valid numbers (not text formatted as numbers) and that Column B uses a clear '0' (zero) to accurately trigger the formula reset.

Solution 1Recommended

Use a Simple IF Formula for the Running Total

This is the most straightforward method, utilizing a basic IF statement to check for a zero and reset the cumulative sum accordingly.

Instead of using complex array formulas, you can reference the cell directly above the current one. This simple logical check is highly efficient for large datasets.

Set the starting value

Click on the first cell for your running total (e.g., C2). Type `=A2` and press Enter to establish the starting number.

Enter the conditional IF formula

In the cell directly below it (e.g., C3), enter the formula `=IF(B3=0, A3, C2+A3)`. This tells Excel to restart the sum at A3 if B3 is zero; otherwise, it adds A3 to the previous total in C2.

Apply down the column

Select cell C3, hover over the bottom-right corner until the cursor turns into a plus sign (Fill Handle), and drag it down to apply the conditional running total to the rest of your data.

Formula Customization: If a zero in Column B means the running total should equal 0 instead of restarting with Column A's current value, change the formula to =IF(B3=0, 0, C2+A3).

Solution 2

Use a Dynamic SUM and LOOKUP Formula

An advanced approach that finds the most recent reset row using lookup or array functions and sums from that exact point to the current row.

Advanced Spreadsheet Functions

Create Conditional Running Totals Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical formulas, lookup functions, and array calculations, allowing you to build complex, conditional running totals with zero hassle.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your workbook, and locate the columns you want to sum and trigger.
2. Input the IF formula: Type your conditional formula (e.g., `=IF(B3=0, A3, C2+A3)`) into the running total column.
3. Drag to fill: Double-click the fill handle in the bottom-right corner of the cell to instantly apply the formula to thousands of rows.

100% compatible with Microsoft Excel (.xlsx) file formats and formula syntax.Seamlessly handles complex formulas like IF, SUMIFS, and LOOKUP for advanced data analysis.Lightweight, fast-loading, and runs smoothly on Windows, Mac, and Linux.Built-in templates and intuitive UI to streamline your workflow.

microsoft office alternative - wps office

Frequently Asked Questions

How can I reset a running total when the month changes instead of a zero value?

You can use an IF formula comparing the month of the current row's date to the previous row's date. For example, `=IF(MONTH(B3)<>MONTH(B2), A3, C2+A3)` will reset the sum every time a new month is detected.

Why does my running total formula return a #VALUE! error?

This usually happens if your formula attempts to perform addition on a text string, such as a column header in row 1. Ensure your starting formula in row 2 correctly references numeric values, or use the SUM function (which ignores text) instead of the '+' operator.

Can I reset the sum based on a blank cell rather than a zero?

Yes, you can easily modify the IF condition to check for empty cells. Simply change the logical test from `B3=0` to `B3=""` in your formula, and the running total will restart whenever it encounters a blank cell in column B.

Excel & Data Problems