Formula Errors

Fix Excel IF Formula Errors When Score Cells Are Blank

Maira MehtabMaira Mehtab Nov 14, 2026 869 views

Question details

The user needs scoring formulas to handle blank source cells without producing errors or incorrect totals.

Fix Excel IF Formula Errors When Score Cells Are Blank
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook converts win, draw, and loss entries into points and adds them to running totals.
Observed behavior
Deleting a source entry can cause a total to return an error, and totals may not update correctly when all score entries are blank.
Before you start

Identify the score entry cells, the formulas that convert them to points, and the cells that total those points. Check the sheet names, result labels, and point values in your workbook before adapting the examples.

Solution 1Recommended

Check for blank scores before calculating points and totals

Use this approach when blank score entries should produce blank point cells and totals should appear only after points are entered.

The examples assume Scores!J8 contains Win, Draw, or Loss, worth 3, 1, or 0 points. Replace the sheet name, cell references, labels, and point values with those used in your workbook.

1
Inspect the point formula

On the Running Sheet, select the cell that converts the score to points, such as J21. Check its formula bar to confirm which Scores sheet cell it reads.

2
Add a blank check

In the point cell, adapt this example: =IF(Scores!J8="","",IF(Scores!J8="Win",3,IF(Scores!J8="Draw",1,IF(Scores!J8="Loss",0,"")))). It returns an empty string for a blank or unrecognized entry.

3
Update the total

Select the total cell, such as Q8, and adapt =IF(COUNT(J21,J29)=0,"",SUM(J21,J29)). COUNT keeps the total blank when neither point cell contains a number; SUM adds numeric points while ignoring empty strings.

4
Test each result

On the Scores sheet, enter and delete a Win, Draw, and Loss value in the source cell. Check that the point cell and total update as expected, including when all referenced point cells are blank.

Check for blank scores before calculating points and totals
Check unexpected entries: The example returns a blank for labels other than Win, Draw, or Loss. SUM ignores text but does not hide errors already present in referenced cells.
Work with scoring formulas in WPS

Handle blank score cells in WPS Spreadsheet

Open your scoring workbook in WPS Spreadsheet and use IF, COUNT, and SUM formulas to leave empty scores blank while calculating entered results.

  1. 1. Open the workbook: In WPS Spreadsheet, select Open and choose your scoring workbook.
  2. 2. Update the point formula: Select the point cell and use the formula bar to add an IF check for its blank source cell before the win, draw, and loss logic.
  3. 3. Update the total: Select the total cell and use COUNT and SUM to keep it blank until at least one referenced point cell contains a number.
  4. 4. Test and save: Enter and delete score values on the source sheet, check the results, and select Save.
Check blank score cells before assigning points.Calculate totals while ignoring empty-string results.Open and edit Microsoft Excel XLS and XLSX files.Use a familiar spreadsheet interface for reviewing formulas.
microsoft office alternative - wps office

Frequently Asked Questions

Why does adding two point cells return an error when one is blank?

A formula may return an empty string rather than a truly empty cell, and direct addition can encounter text. Try SUM(J21,J29), then inspect the referenced cells for existing errors if the total still fails.

How can I keep the total blank until a score is entered?

In the total cell, use a formula such as =IF(COUNT(J21,J29)=0,"",SUM(J21,J29)). Adjust the references to include your point cells.

Will a zero-point loss be included in the total?

Yes. COUNT treats numeric zero as a number, so a loss worth 0 points can make the total appear even if the other point cells are blank.

Which formula should I inspect first?

Select the point cell that reads the source score, such as J21, and inspect the formula bar. Then select the total cell, such as Q8, to see how it combines point cells.