Excel SUM Ignore #N/A Errors: Practical Fixes and Formulas

Errors & fixes · Updated November 18, 2026

Yes. In Microsoft Excel for Microsoft 365, if the values to total are in B2:B10, use =SUM(IFNA(B2:B10,0)) to treat #N/A entries as zero while adding the numeric values. If you want a version that does not depend on array handling, use =AGGREGATE(9,6,B2:B10); both formulas skip errors in that range rather than fixing the errors in the source cells.

Why SUM returns an error

SUM adds numeric values in its arguments, but it does not silently discard an error contained in a referenced range. If even one cell in the range contains #N/A, the SUM result is usually #N/A too. This is Excel signaling that part of the requested calculation could not be completed, not a sign that SUM has added the visible numbers incorrectly.

#N/A commonly appears when a lookup cannot find a requested item, or when a formula deliberately returns #N/A to mark unavailable data. For instance, a sales list might have amounts for some products and #N/A for a product whose lookup has no match yet. You may still need a subtotal for the amounts that are available. In that situation, decide whether a partial total is appropriate, then use a formula that handles the error explicitly.

It is useful to distinguish an actual blank cell from an error. SUM normally skips blanks and text in referenced cells, but an error is neither a blank nor an ordinary text value. Wrapping the range with an error-handling function changes how the calculation treats those cells.

Choose a method based on which errors to ignore

For a Microsoft 365 workbook, IFNA is often the most precise choice when #N/A is the only error to skip. IFNA replaces #N/A with a value you specify—in this case, zero—while leaving other errors visible. That matters when a #VALUE!, #REF!, or #DIV/0! error indicates a different problem that should not disappear inside a plausible-looking total.

Use IFERROR instead when the instruction really is to ignore every error type in the range. It catches #N/A as well as other Excel errors, so it can be convenient for messy imported data, but it can also conceal a broken reference or an invalid calculation. AGGREGATE is an alternative when you want SUM behavior with an option to ignore error values, without wrapping the range in IFNA or IFERROR.

  • Choose IFNA when only missing or unavailable values marked #N/A should count as zero.
  • Choose IFERROR when all error values should be excluded from the total and you have checked that hiding them is acceptable.
  • Choose AGGREGATE when you want a direct sum that ignores errors in a referenced range.
  • Repair the source formula or lookup when an error means the underlying data is wrong and a partial total would be misleading.

Use IFNA to skip only #N/A

Assume B2:B10 contains numbers and possibly #N/A values, and you want other error types to remain visible. In Excel for Microsoft 365, enter this in an empty result cell:

With the values in B2:B10, use =SUM(IFNA(B2:B10,0)).

IFNA substitutes zero for each #N/A in the range, and SUM adds what remains. For example, if B2:B5 contains 12, #N/A, 8, and 5, the result is 25: 12 + 0 + 8 + 5. A #VALUE! elsewhere in B2:B10 would still make the result an error, which helps reveal issues other than missing data.

In Microsoft 365, enter the formula with Enter; dynamic-array calculation lets IFNA process the range as an array inside SUM. In older Excel versions, formulas that pass a range through an array operation may require Ctrl+Shift+Enter rather than Enter. If you use an older release, test the formula in that version or use AGGREGATE where available. On Excel for Mac, the legacy array-entry shortcut is Control+Shift+Return; the Microsoft 365 version normally needs only Return.

Use IFERROR when every error should be skipped

If all errors in the input range should be treated as zero, use IFERROR. This can be useful when a report is intended to show the sum of currently usable numeric results, regardless of why some cells have errors.

With values in B2:B10 and a deliberate decision to suppress every error type in the total, use =SUM(IFERROR(B2:B10,0)).

For the example values 12, #N/A, 8, and 5, the formula returns 25. It would also skip a #VALUE! or #REF! in the same range. That is the important trade-off: the total may look complete even when a formula has broken. A safer workflow is to keep a separate error check, fix errors at their source, or use IFNA when only #N/A is expected.

Neither IFNA nor IFERROR changes the original cells. They only affect the result of this particular formula. Other formulas that refer directly to an error cell can still return an error.

Use AGGREGATE as a direct alternative

AGGREGATE can calculate a sum while ignoring errors in a range. Its first argument selects the operation, and 9 means SUM. The second argument, 6, tells it to ignore error values. With the data in B2:B10, enter:

With the values in B2:B10, use =AGGREGATE(9,6,B2:B10).

If B2:B5 contains 12, #N/A, 8, and 5, this returns 25. Unlike the IFNA example, this setting ignores errors generally, not just #N/A. AGGREGATE is available in Excel 2010 and later, including Microsoft 365, and this reference-form formula does not need Ctrl+Shift+Enter.

Do not confuse ignoring errors with ignoring hidden rows. The option 6 in this formula means ignore error values; it does not mean exclude rows hidden by filtering or manually hidden. AGGREGATE has other options for handling hidden rows, but select one only if that is part of the calculation you actually need. A standard SUM, IFNA, or IFERROR formula also includes numeric values in rows hidden by a filter.

What these formulas do to blanks, text, and other errors

For the range-based examples above, an ordinary blank cell contributes nothing to SUM, and text stored in a referenced cell is generally not added as a number. A text value that looks like a number may also be treated differently from a true numeric value, depending on how it is supplied to the calculation. If a total seems too small even after handling #N/A, check whether the amounts are numbers or text.

The formulas also differ in how they handle errors. IFNA changes only #N/A to zero, so other error types can still flow through to the result. IFERROR changes every error in the input array to zero. AGGREGATE with option 6 ignores error values for the selected operation. None of these approaches identifies why an error occurred or corrects the formula that produced it.

Another common misconception is that adding zero to the range will make errors disappear. It will not: arithmetic with an error normally returns an error. The error must be handled with a function such as IFNA or IFERROR, or ignored through an appropriate AGGREGATE option.

Check the result and preserve useful error signals

A total that skips #N/A is a partial total whenever some entries are unavailable. Before relying on it, confirm that this is what the report should communicate. If #N/A means a product has not been matched to a price, for example, the subtotal may omit real sales rather than simply ignore irrelevant records.

Useful checks include:

  • Inspect a few source cells to confirm that #N/A means an expected missing value, rather than a failed lookup that needs correction.
  • Compare the count of error cells with the number of records you expect. A rising error count can explain a sudden change in totals.
  • Keep a visible note or separate status column showing that the reported sum excludes unavailable entries.
  • Test the calculation with a small known example, including at least one number and one #N/A, before copying it through a report.

If the workbook is used for reconciliation, inventory, or another process where every record must be accounted for, correcting the lookup or source data is usually better than suppressing errors. For exploratory analysis, a partial sum may be exactly what is needed, provided readers can tell that some values were excluded.

Common problems and fixes

If the IFNA formula still returns an error, check whether the range contains an error other than #N/A. IFNA deliberately leaves those errors alone. If skipping all errors is intended, switch to IFERROR or AGGREGATE; otherwise, investigate the remaining error rather than hiding it automatically.

If the formula returns zero or a smaller number than expected, verify that the cells contain numeric values, not numbers stored as text. Also check that the selected range includes the full data set and that the source cells do not contain formulas returning empty text. Filtering rows does not make these formulas exclude them; hidden numeric values remain part of the total.

If an older Excel version rejects the IFNA array formula or does not calculate it as expected, use the AGGREGATE formula if that version supports it, or enter the array formula with the legacy array-entry shortcut. In Excel for the web and current Microsoft 365 desktop versions, the IFNA range formula can normally be entered with Enter. Mac users should use the corresponding Return key for ordinary entry, and Control+Shift+Return only when a legacy array formula requires it.

Frequently Asked Questions

Can SUM ignore only #N/A and still show other errors?

Yes. If the values are in B2:B10, use =SUM(IFNA(B2:B10,0)). IFNA replaces #N/A with zero but does not suppress other error types, so a different error can still appear in the result.

Does SUMIF ignore #N/A automatically?

No. SUMIF is not a general error-handling substitute, and an error in a referenced range can still affect a calculation. Use IFNA, IFERROR, or AGGREGATE when your specific goal is to handle error values.

Will the formula change or delete the #N/A cells?

No. The formula only changes how its own result is calculated. The original error cells remain unchanged, and other formulas that use them may still return errors.

Does AGGREGATE ignore filtered-out rows too?

Not with =AGGREGATE(9,6,B2:B10); option 6 ignores errors, not hidden rows. If filtered or hidden rows must also be excluded, choose an AGGREGATE option designed for hidden rows and confirm it matches the report’s intended behavior.

Which method should I use in Excel 365?

Use IFNA when #N/A is the only error that should be skipped. Use IFERROR only when all error types should be suppressed, and use AGGREGATE when you prefer its built-in ignore-errors option for a referenced range. In every case, make sure excluding those values gives a truthful total.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.