How to Show Running Totals Only When Scores Are Entered in Excel
Question details
The user wants to calculate a running total that only displays when new data is entered, leaving future rows blank instead of repeating the last calculated total.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a sports score tracker or financial ledger where running totals should not populate ahead of actual data entry.
- Observed behavior
- The standard running total formula repeats the previous result down through blank rows where no scores have been entered yet.
Ensure your data is organized in clear columns (e.g., Home Score, Home Total, Away Score, Away Total) and identify the exact cell references for your first row of data.
Use the IF and N Functions to Hide Totals in Blank Rows
This method checks if a score has been entered before calculating the running total, returning a blank cell if no data exists.
The N function converts non-number values (like blank cells) to zero. By combining it with the IF function, you can instruct the spreadsheet to only perform the SUM operation when the score cells contain actual numbers.
Click on the cell where you want the first running total to appear (for example, C10 for the Home Total).
Type the formula =IF(N(B10)+N(D10),SUM(B10,C9),""). Replace B10 and D10 with your current row's score cells, and C9 with the previous row's total cell.
Press Enter to calculate the result for the current row.
Click and drag the fill handle (the small square at the bottom-right of the selected cell) down the column to apply this dynamic formula to all future rows.
Calculate Running Totals Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical functions like IF, N, and SUM, allowing you to build dynamic score trackers and financial ledgers with ease. It provides a familiar interface and seamless formula execution.
- 1. Open your workbook: Launch WPS Spreadsheet and open your score tracking or ledger file.
- 2. Select the total cell: Click on the cell in the totals column where the calculation should begin.
- 3. Input the dynamic formula: Type =IF(N(B10)+N(D10),SUM(B10,C9),"") and adjust the cell references to match your sheet.
- 4. Fill down the column: Drag the fill handle downwards to apply the running total logic to all blank rows.
Frequently Asked Questions
Why does my running total show the same number repeatedly?
A basic running total formula doesn't check if new data exists in the current row. If the row is blank, it simply adds zero to the previous total, causing the same number to repeat down the column. Wrapping the calculation in an IF statement solves this issue.
What does the N function do in Excel formulas?
The N function translates values into numbers. It converts TRUE to 1, FALSE to 0, and text or blank cells to 0. In running totals, it ensures that blank score cells are treated as zeros so the IF condition can evaluate them properly without returning an error.
Can I use Conditional Formatting to hide repeating totals instead?
Yes, you can use Conditional Formatting to change the font color to match the background (e.g., white text) if the current cell equals the cell above it. However, using the IF formula is generally preferred because it keeps the cell truly empty, which prevents errors in downstream calculations or charts.
