No. In Excel for Microsoft 365, SUBTOTAL does not ignore error values in rows it includes: a visible #N/A or #VALUE! can make the result an error too. To calculate a subtotal while skipping errors, use AGGREGATE with its error-ignoring option, such as =AGGREGATE(9,6,B2:B7).
What SUBTOTAL actually does
SUBTOTAL is designed to calculate a result over a list while responding to filtered and hidden rows. It can calculate a sum, average, count, maximum, minimum, and other common results. Its name can be misleading: it handles other SUBTOTAL formulas specially, but it does not treat ordinary error values as items to skip.
The first argument selects the calculation. For example, 9 means SUM, while 1 means AVERAGE. The function number also controls whether manually hidden rows are included:
- Function numbers 1 through 11 include manually hidden rows, but exclude rows removed by a filter.
- Function numbers 101 through 111 exclude both manually hidden rows and rows removed by a filter. The final two digits select the same calculation as the corresponding number from 1 through 11.
Both sets of function numbers ignore nested SUBTOTAL formulas in the referenced range. This prevents a grand total from counting a subtotal twice. That behavior is different from ignoring an error such as #N/A: a cell containing an error is still part of the calculation if its row is included.
Why an error can break the subtotal
SUM can add numeric values, but an error in the input range is not a number it can safely add. If an included cell contains #N/A, #VALUE!, #DIV/0!, or another Excel error, a SUBTOTAL using SUM can return an error rather than a numeric total. The same general issue applies when an included error is in the range used by another SUBTOTAL calculation, such as an average.
For example, assume B2:B7 contains 10, #N/A, 20, #VALUE!, 30, and 40. With those values in the range, this formula returns an error:
With the six values above in B2:B7, the formula is =SUBTOTAL(9,B2:B7); it does not return 100 because the range includes errors.
Filtering the list does not change the basic rule. SUBTOTAL excludes rows removed by a filter, so an error in a filtered-out row does not affect that calculation. But an error in a row that remains visible can still cause an error result. This distinction explains why a subtotal may work for one filter selection and fail after the filter changes.
Use AGGREGATE to skip error values
AGGREGATE offers a calculation choice and an options choice. For a sum, function number 9 selects SUM. Option 6 tells Excel to ignore error values. With the sample values in B2:B7, use:
With 10, #N/A, 20, #VALUE!, 30, and 40 in B2:B7, =AGGREGATE(9,6,B2:B7) returns 100: it adds the four numeric values and skips both errors.
For AGGREGATE functions that use a reference such as B2:B7, filtered-out rows are excluded. Option 6 is specifically for error values; it does not, by itself, exclude manually hidden rows. If you want the sum to ignore errors and manually hidden rows too, choose option 7 instead. The same sample range returns 100 with =AGGREGATE(9,7,B2:B7), assuming no additional visible numeric values are involved.
AGGREGATE can also calculate averages, counts, and other results; select the function number that matches the calculation you need. Its options determine which complications to ignore, including errors and, depending on the option, hidden rows or nested SUBTOTAL and AGGREGATE functions. Choose the option deliberately rather than assuming every option skips the same things.
Choose the right hidden-row behavior
Before replacing a formula, decide what your total should mean when the list is filtered or rows are hidden manually. This matters in reports where someone may hide a row to exclude it temporarily, or filter a table to see one category at a time.
- Use SUBTOTAL when you need a result that responds to filters and you want errors to remain visible as a warning that the source data needs attention.
- Use AGGREGATE with option 6 when you want to exclude error values but still include manually hidden rows.
- Use AGGREGATE with option 7 when you want to exclude errors and manually hidden rows as well.
- Check how filtered rows behave in your chosen formula. In AGGREGATE’s reference form, rows excluded by a filter are not included in the calculation.
A skipped error can make a report look complete even when some records were not counted. If an error signals a missing price, failed lookup, or invalid quantity, excluding it may be reasonable for a quick total, but it does not repair the underlying data. Consider displaying a separate error count or investigating the cells before relying on the result.
Alternatives and their trade-offs
If the requirement is simply to add numeric values while treating errors as zero, a Microsoft 365 formula using SUM and IFERROR is another option. With the same sample values in B2:B7, =SUM(IFERROR(B2:B7,0)) returns 100. IFERROR substitutes zero for each error within the calculation, and SUM then adds the resulting values.
Unlike SUBTOTAL or the reference form of AGGREGATE, that SUM formula does not automatically provide filter-aware subtotal behavior. It sums the range regardless of whether its rows are filtered out or manually hidden. Use it only when including every row is intended. It also changes the meaning of errors for that calculation: each one is treated as zero, not fixed at its source.
Another option is to correct the source cells or add a helper column that identifies valid numeric records. That takes more setup but can make exclusions explicit and auditable, especially when a skipped value should be reviewed rather than silently omitted. Avoid wrapping every source formula in IFERROR with a blank or zero merely to make a report look tidy; doing so can conceal lookup failures and other problems that deserve attention.
Common problems to check
If your formula still returns an error, first confirm that the error is in the range you intended to calculate. A formula may include extra rows, or the error may come from a different cell referenced by the calculation. Then check that you are using AGGREGATE’s reference form with the correct range and option, rather than expecting SUBTOTAL to skip errors.
Also verify that the filter is actually excluding the problematic row. A row that looks hidden may be manually hidden rather than filtered out, and the two cases can be treated differently. In AGGREGATE, option 6 ignores errors but does not ignore manually hidden rows; option 7 does both. In SUBTOTAL, the 1–11 and 101–111 function-number groups determine whether manually hidden rows are included.
Finally, distinguish error values from text that looks like an error. A cell containing the text #N/A is not the same as an actual #N/A error, and formulas may handle those differently. If totals remain unexpected, inspect the cell itself and its formula bar, then check the range boundaries and the rows currently included by filters.
Frequently Asked Questions
Does SUBTOTAL ignore #N/A?
No. If the #N/A error is in an included row, SUBTOTAL does not skip it and may return an error. Use AGGREGATE with option 6 if you need to exclude error values.
Does SUBTOTAL ignore errors in filtered-out rows?
Rows removed by a filter are excluded from a SUBTOTAL calculation. An error in a row that remains included can still affect the result, so changing the filter can change whether the formula returns a number or an error.
What is the AGGREGATE formula for a sum that ignores errors?
Use =AGGREGATE(9,6,range), replacing range with your cell range. The 9 selects SUM, and option 6 ignores error values. For example, =AGGREGATE(9,6,B2:B7) sums the usable numeric values in B2:B7.
What is the difference between SUBTOTAL 9 and 109?
Both select SUM. In a SUBTOTAL formula, 9 includes manually hidden rows while 109 excludes them. Both exclude rows removed by a filter and ignore nested SUBTOTAL formulas.
Should I ignore errors or fix them?
That depends on what the errors mean. Skipping them can be useful for a provisional total, but it can also omit records that should count. If an error represents missing or invalid source data, investigate it or clearly report that some values were excluded.
Related
- Excel Max Absolute Value: Find the Largest Magnitude
- Excel Mini Calendar and Date Picker: Your Options
- Excel Min Sec Format: Display Minutes and Seconds
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Merge Cells Shortcut and Safe AlternativesGoogle Sheets has no dedicated keyboard shortcut that directly merges selected cells. In a browser, select the cells…
- How to Mail Merge in Word 2010: A Step-by-Step GuideTo mail merge in Word 2010, open the Mailings tab, choose Start Mail Merge or Step-by-Step Mail Merge…
- Excel Dropdown: Remove Duplicate Options EasilyTo remove duplicate choices from an Excel dropdown, clean the list that feeds it: select that source list,…
- Excel Insert Blank Row Every Other Row: Methods That WorkTo insert a blank row between every pair of records in Excel, use a short VBA macro for…
- Google Sheets COUNTIF: Count Cells Containing TextWith the text to check in A2:A100, count cells containing a word or phrase anywhere in the cell…
- Scenario Analysis in Excel: Example and Setup GuideA scenario analysis Excel example compares how a result changes when you vary key assumptions. For a simple…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.