To add a range while treating error cells as zero, use =SUM(IFERROR(A2:A10,0)), assuming the values and any errors are in A2:A10. In Excel for Microsoft 365, IFERROR replaces each error in that range with zero, and SUM adds the remaining numbers.
Why SUM can return an error
SUM can add numbers in a range that also contains blank cells or text, but an Excel error value in the range normally makes the entire SUM result an error. For example, if A2 contains 12, A3 contains the error #N/A, and A4 contains 8, =SUM(A2:A4) returns #N/A rather than 20. The error may have originated in another formula, such as a lookup that could not find a match or a division whose denominator is zero.
This behavior is useful when an error signals that a result is incomplete or untrustworthy: Excel does not silently produce a total that looks valid. But for a summary where unavailable entries should contribute nothing, allowing one error to block the whole total can make the worksheet less useful. IFERROR offers a way to choose how the formula handles that situation.
Use IFERROR on the range before summing
Assume A2:A6 contains 100, #N/A, 25, #DIV/0!, and 50, respectively. To replace errors in those cells with zero and add the numbers that remain, use:
=SUM(IFERROR(A2:A6,0))
The result is 175: IFERROR changes the two errors to zero, so SUM calculates 100 + 0 + 25 + 0 + 50. The original cells are not changed; only the values evaluated inside this formula are substituted. This distinction matters if other formulas or reports still need to display the errors in the source range.
The formula can be adapted to other ranges. If your data is in C5:C30, replace A2:A10 with C5:C30. The second IFERROR argument is the replacement value. Using 0 is appropriate when an error should add nothing to a total; replacing errors with another number would change the meaning of the calculation and should only be done when that number is justified.
What IFERROR changes, and what it leaves alone
IFERROR has two arguments: the value to evaluate and the value to return if that evaluation produces an error. In this pattern, the first argument is a range and the second is zero. Microsoft 365 evaluates the range as an array, handling each item before SUM adds the results. A valid number stays as it is; an error becomes zero.
IFERROR does not turn every unusual-looking value into zero. A cell containing the text N/A, for example, is text, not the #N/A error value, so IFERROR leaves it unchanged. Likewise, blank cells remain blank in the evaluated array. SUM ordinarily ignores text and blanks in a referenced range, so they do not add to the total. The formula is specifically a way to handle actual Excel errors, not a general way to clean or convert data.
IFERROR catches different kinds of Excel errors, not only #N/A. That broad handling is convenient when a range may contain several error types, but it can also conceal a problem that deserves investigation. Consider whether a zero is a sensible substitute for every possible error in the range before using the formula in an important report.
Why wrapping the whole SUM is different
A common alternative is to handle an error only after SUM has calculated. Assume A2:A6 contains 100, #N/A, 25, #DIV/0!, and 50. This formula wraps the overall sum:
=IFERROR(SUM(A2:A6),0)
Its result is 0, because SUM encounters errors and returns an error, which the outside IFERROR then replaces with zero. It does not return 175. The distinction is important: =SUM(IFERROR(A2:A6,0)) handles individual range entries before adding, while =IFERROR(SUM(A2:A6),0) replaces a failed total as a whole.
Use the outer IFERROR version when the desired rule is “show zero if this entire calculation fails.” Use IFERROR inside SUM when the rule is “ignore error entries and add the valid numbers.” Those are different reporting choices, even though both formulas contain the same function name.
Choosing an error-handling approach
The right formula depends on what the error means in the workbook. Before suppressing it, identify whether the error is an expected missing result or evidence of a formula, reference, or data problem. These guidelines help make the choice explicit:
- Errors should contribute nothing: use
=SUM(IFERROR(A2:A10,0))when valid values should still count and error entries should add zero. - Any error invalidates the entire total: keep the ordinary SUM result visible, or use an outer IFERROR only if returning a fallback such as zero is the agreed reporting rule.
- Only missing lookups should be treated as zero: use IFNA rather than IFERROR so other kinds of errors remain visible.
- The source data should be corrected: fix the formulas or inputs that create the errors instead of hiding them in the summary.
For a narrower alternative, assume A2:A10 contains values and possible errors, and only #N/A should be replaced with zero before summing. Use =SUM(IFNA(A2:A10,0)). IFNA handles #N/A, but other errors, such as #DIV/0! or #REF!, can still make the total return an error. That narrower behavior is useful when a missing lookup is expected but other failures should remain apparent.
Common problems and checks
If the formula still returns an error, inspect the range and the formula itself. A frequent cause is an error type that was not handled by a narrower function such as IFNA. With IFERROR, check that the formula actually wraps the range being summed and that the range does not include an error through a separate calculation outside that expression. Also verify that you selected the intended cells; a formula that points to the wrong column may produce a technically valid but misleading total.
If the result seems too small, remember that errors have become zero and therefore contribute nothing. Check for numbers stored as text or values outside the selected range as well. SUM does not normally add text entries from referenced cells, even when the text resembles a number. IFERROR does not convert that text into a numeric value.
A displayed zero can also be ambiguous: it might mean the valid values add up to zero, or that the formula replaced a failed overall calculation with zero. This is one reason not to use =IFERROR(SUM(A2:A10),0) automatically. For a transparent report, consider displaying a separate count of errors or keeping a visible status message alongside the total, so readers can tell whether the result included all expected inputs.
Excel version and entering the formula
In Microsoft 365 for Windows, enter the formula in the cell where you want the total and press Enter. The range version of IFERROR returns an array of evaluated values for SUM to process, so you do not need to select multiple cells or enter a legacy array formula. The formula also works in current Microsoft 365 for Mac and Excel for the web.
In older Excel releases without dynamic-array behavior, a formula that passes a range through IFERROR may need to be confirmed as an array formula with Ctrl+Shift+Enter on Windows. If you are maintaining a workbook for an older version, check how that version evaluates the formula. An alternative is to add a helper column that uses IFERROR on each row individually, then use SUM on that helper column; this is less compact but makes each replacement easy to inspect.
When entering the formula, keep the comma between IFERROR arguments in standard English-language Excel syntax. Some regional settings use different list separators, so Excel may require a semicolon instead of a comma depending on the computer’s configuration. The formula logic is unchanged, but use the separator your copy of Excel accepts.
Frequently Asked Questions
Does SUM ignore errors in Excel?
No. SUM usually ignores blank cells and text in a referenced range, but an error value normally causes SUM to return an error. To add the valid numbers while replacing error entries with zero, use =SUM(IFERROR(A2:A10,0)), assuming the data is in A2:A10.
What is the difference between SUM(IFERROR(range,0)) and IFERROR(SUM(range),0)?
The first formula replaces errors within the range before SUM adds the values, preserving the valid numbers in the total. The second replaces the result of the entire SUM if it errors, so it returns the fallback value rather than a partial total. With a fallback of zero, an error anywhere in the range can make the second formula return zero.
Can I use IFNA with SUM instead of IFERROR?
Yes. If the range is A2:A10 and only #N/A should be treated as zero, use =SUM(IFNA(A2:A10,0)). IFNA does not suppress other error types, which can be helpful when those errors should remain visible.
Does IFERROR change the original cells?
No. IFERROR changes the values used in the formula’s calculation, not the contents of the source cells. A source formula that displays #N/A will still display #N/A unless you edit that formula or apply a separate display treatment.
Will this formula turn text or blank cells into zero?
No. IFERROR replaces error values, not ordinary text or blanks. SUM generally ignores text and blank cells in a referenced range, so they do not contribute to the total. If text-formatted numbers need to count, convert or clean those entries separately.
Related
- Excel Budget Spreadsheet Free? Find Templates and Tips
- Excel Accounting Format: How It Works and When to Use It Properly
- Excel Accounting Format: Show Negative Numbers in Brackets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP Entry Examples: Formulas for Common SearchesXLOOKUP entry examples show how to find a value in one range and return its matching value from…
- Excel LET: Can It Use Multiple Variables?Yes. Excel’s LET function allows multiple named values in one formula: write each name and value as a…
- Excel Cell Contains String? Find Text With a FormulaTo check whether a cell contains a particular string of text, use =ISNUMBER(SEARCH("apple",A2)). With the word apple anywhere…
- How to Install XLOOKUP in Excel and Check CompatibilityYou do not install XLOOKUP as a separate Excel feature or add-in: it is already built into supported…
- Sort an Excel Column Alphabetically Without Mixing RowsTo sort a column alphabetically in Excel for Microsoft 365 on Windows, click a cell in the column…
- XLOOKUP Multiple Criteria: Examples and Common FixesTo use XLOOKUP with multiple criteria in Excel, multiply the criteria tests and look for 1: =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:C100,"Not found").…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.