Formula Errors

How to Fix SUM, IFERROR, and COUNT Formula Errors in Excel

Bushra Parveen Oct 15, 2026 871 views

Question details

The user is experiencing unexpected calculation results and #VALUE! errors when evaluating ranges that include empty text strings returned by the IFERROR function.

How to Fix SUM, IFERROR, and COUNT Formula Errors in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating totals, counting records, or performing lookups on data ranges that contain formulas outputting empty text strings instead of truly blank cells.
Observed behavior: Using arithmetic operators like the plus sign (+) returns a #VALUE! error when encountering text strings, and functions like COUNT may fail to count the intended cells.

Before you start

Identify which cells in your calculation range contain formulas like IFERROR that might be returning empty text strings ("") instead of truly blank cells, as these are treated as text by Excel.

Solution 1Recommended

Replace Arithmetic Plus Signs with the SUM Function

The SUM function safely ignores text values, including empty strings returned by IFERROR, preventing #VALUE! errors.

When you use a plus sign (+) to add cells, Excel expects numerical values. If a cell contains text—even an invisible empty text string ("") generated by an IFERROR formula—Excel returns a #VALUE! error. The SUM function natively ignores these text values, making it the safer alternative for mixed data ranges.

Locate the error

Find the cell returning the #VALUE! error that currently uses standard addition syntax, such as =A1+B1 or =Data!V4+C22.

Change the formula to SUM

Replace the plus signs with commas and wrap the cell references in the SUM function. For example, change it to =SUM(Data!V4, C22:C26).

Apply the new formula

Press Enter to apply the formula. The SUM function will now sum the numeric values and safely ignore any empty text strings.

Replace Arithmetic Plus Signs with the SUM Function

Formula Validated: By using the SUM function syntax, your calculations will remain accurate even if source cells change from numbers to empty strings in the future.

Solution 2

Correctly Configure VLOOKUP Ranges

Ensure your VLOOKUP formula correctly references the lookup array, as single-row arrays are entirely valid if properly structured.

Solution 3

Use the COUNT Function Correctly for Numbers

Understand how the COUNT function distinguishes between numbers, zeros, text, and blanks to ensure accurate data tallies.

Advanced Formula Management

Easily Manage Complex Formulas with WPS Spreadsheet

WPS Spreadsheet provides a robust calculation engine and intuitive error-checking tools, making it incredibly simple to execute SUM, VLOOKUP, and COUNT functions without stumbling over #VALUE! errors. It flawlessly interprets IFERROR text strings and is highly compatible with existing Excel documents.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your problematic formulas.
2. Access the Formula tab: Navigate to the 'Formulas' tab on the top ribbon to access the built-in function library and error-checking tools.
3. Use Insert Function: Click 'Insert Function' to search for SUM or COUNTA. The guided prompts will help you select the correct arguments without syntax errors.
4. Evaluate errors: Use the 'Error Checking' button in the ribbon to automatically find and diagnose #VALUE! errors in your worksheet.

Fully compatible with Microsoft Excel (.xlsx) formats and standard formula syntaxBuilt-in error checking instantly highlights logic and referencing issuesIntuitive Insert Function dialog for guided SUM, VLOOKUP, and COUNT setupFree, lightweight, and fast application for seamless spreadsheet management

microsoft office alternative - wps office

Frequently Asked Questions

Why does my addition formula return a #VALUE! error?

Using the plus sign (+) to add cells that contain text, including invisible empty text strings ("") generated by an IFERROR formula, results in a #VALUE! error. You should use the SUM function instead, as it automatically ignores text values.

Can a VLOOKUP range be just one row?

Yes, a one-row by two-column range (such as AF26:AG26) is perfectly valid in a VLOOKUP formula. You just need to ensure that the lookup value exists in the first column of that selected range.

Does the COUNT function include blank cells or text?

No, the standard COUNT function only tallies cells containing numeric values, including zero. It does not count text, empty text strings from formulas, or truly blank cells. If you need to count cells containing text, use the COUNTA function instead.

Excel & Data Problems