How to Reset a Running Total After Zero in Excel (Fix #REF! Error)
Question details
The user needs a formula to calculate a running total that restarts from zero every time a zero is entered in the dataset, while avoiding a #REF! error when the most recently entered value is a zero.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a continuous running total across a row where inputs update regularly. The sum must restart after any single or consecutive zeros are entered.
- Observed behavior
- The current formula correctly resets the total after a zero in the middle of the range, but returns a #REF! error if the final value in the row is a zero, breaking dependent formulas.
Ensure your dataset is arranged in a continuous single row or column without merged cells, and verify that blank cells are not accidentally being evaluated as zeros by other formulas.
Use INDEX, MATCH, and IFERROR to Sum Values After the Last Zero
This solution dynamically locates the position of the last zero in your range and sums only the numbers that follow it, using IFERROR to handle the edge case where the final inputted number is zero.
To reset a running total, the formula must identify the relative position of the last zero inputted. By building the calculation around this position, you can instruct Excel to only sum values appearing after it.
When the final value in a row is zero, the formula tries to reference a starting position for the sum that does not exist yet, causing a #REF! error. Wrapping the entire logic in an IFERROR function elegantly catches this error and outputs 0 as intended.
Use an array-based lookup approach to find the position of the most recent zero. For example, if your data is in row 1 (A1:G1), use MATCH(2, 1/(A1:G1=0)) to pinpoint the column of the last zero.
Combine the SUM and INDEX functions to add values starting from the cell immediately after the last zero. The core formula looks like: SUM(INDEX(A1:G1, MATCH(2,1/(A1:G1=0))+1):G1).
To prevent the #REF! error when the last entry is zero, wrap your formula with IFERROR. Select your target cell and enter: =IFERROR(SUM(INDEX(A1:G1, MATCH(2,1/(A1:G1=0))+1):G1), 0).
Depending on your Excel version, you may need to press Ctrl + Shift + Enter to execute this array formula properly. Once confirmed, drag the fill handle to apply it to other cells.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides a robust, fast, and highly compatible environment to handle advanced array formulas, dynamic running totals, and complex error handling.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your workbook, and navigate to the worksheet containing your running totals.
- 2. Input the dynamic formula: Select the cell for your running total and type in the IFERROR and INDEX combination exactly as you would in Microsoft Excel.
- 3. Diagnose errors visually: If you encounter unexpected results, navigate to the 'Formulas' tab and click 'Error Checking' to trace precedents and evaluate the calculation step-by-step.
Frequently Asked Questions
Why does a final zero cause a #REF! error in my formula?
When the very last value in your evaluated range is a zero, formulas designed to sum the 'subsequent' cells attempt to reference a cell outside the valid range (since no values exist after the final entry yet). This invalid reference triggers the #REF! error.
Does IFERROR hide other important formula mistakes?
Yes, IFERROR acts as a blanket catch-all for any error, including #N/A, #VALUE!, and #DIV/0!. If you want to exclusively target the reference issue, you can use the IF function combined with ISREF instead of IFERROR.
Will this running total formula work if there are blank cells in the row?
Standard lookup formulas might treat completely blank cells as zeros depending on your settings. It is recommended to ensure empty cells remain truly blank and input actual '0' values only where you want the sum to reset.
