In Excel, use the AGGREGATE function with function number 9 to calculate a sum; choose an option such as 6 to ignore error values. For example, with numbers in B2:B10, =AGGREGATE(9,6,B2:B10) adds the numeric values while excluding errors in that range.
What AGGREGATE means in Excel
AGGREGATE is a built-in Excel function that applies a calculation to a range or array. It can perform 19 operations, including SUM, AVERAGE, COUNT, LARGE, and SMALL. Its practical advantage over many ordinary functions is that you can specify whether to disregard errors, hidden rows, or results from nested SUBTOTAL and AGGREGATE calculations.
For a sum, the important function number is 9. AGGREGATE is not a separate kind of arithmetic: when it is told to sum, it adds numbers much as SUM does. The extra option argument determines which values or rows to leave out. This is useful when a worksheet contains imported data with errors, or when you want a calculation that responds to filtered or hidden rows.
The function is available in Excel 2010 and later, including Microsoft 365 for Windows. The same function and formula structure are available in current Excel for Mac and Excel for the web. Depending on regional settings, Excel may display a different argument separator; the examples here use commas.
Syntax and the SUM function number
The reference form of AGGREGATE takes a function number, an option number, and one or more references. For a sum, use 9 as the first argument. The second argument is a number from 0 to 7 that selects what Excel should ignore. The remaining arguments identify the ranges or cells to calculate.
With sales values in B2:B10, =AGGREGATE(9,6,B2:B10) sums those values and ignores errors in the referenced range. This reference-form formula is the usual choice for a straightforward range sum. AGGREGATE also has an array form with a different final argument, but that form is mainly relevant to other calculations, such as asking for the k-th largest value.
AGGREGATE does not treat every non-number as an error. Like SUM, a referenced range’s text and blank cells are not added as numbers. The option setting matters especially when a cell contains an Excel error value, such as #N/A or #DIV/0!, because an ordinary sum of a range containing an error generally returns an error rather than a usable total.
Choosing the right option number
The option argument controls exclusions. These are the available settings for the reference form:
- 0: Ignore nested SUBTOTAL and AGGREGATE results.
- 1: Also ignore hidden rows.
- 2: Also ignore error values.
- 3: Ignore hidden rows, errors, and nested SUBTOTAL or AGGREGATE results.
- 4: Ignore nothing.
- 5: Ignore hidden rows.
- 6: Ignore error values.
- 7: Ignore both hidden rows and error values.
Options 0 through 3 exclude nested SUBTOTAL and AGGREGATE results as well as the other exclusions listed for each setting. Options 4 through 7 do not exclude those nested results. For a common error-tolerant total, option 6 is a straightforward choice. If the total should also omit manually hidden rows, option 7 may be more appropriate.
Choose the setting for the intended meaning of the total, not simply the setting that returns a number. If errors should signal that the underlying data needs attention, silently excluding them can make a total look complete when it is not. You may want a separate error check or a visible note explaining that the sum omits errors.
Worked example: sum while ignoring errors
Suppose B2:B7 contains 120, 80, #N/A, 50, #DIV/0!, and 100. With those values in B2:B7, =AGGREGATE(9,6,B2:B7) returns 350. The arithmetic is 120 + 80 + 50 + 100; the two error cells are ignored. By contrast, =SUM(B2:B7) returns an error because the referenced range contains error values.
This behavior can be helpful with data imported from another system or formulas that sometimes produce errors. It does not repair the underlying cells, replace the errors with zero, or tell you why they occurred. It simply excludes them from this calculation. If the errors indicate missing records or broken formulas, investigate them separately before relying on the total.
AGGREGATE can also sum separate references. With one part of the data in B2:B10 and another in D2:D10, =AGGREGATE(9,6,B2:B10,D2:D10) adds both ranges while ignoring errors in them. This is useful when the ranges are intentionally separate, but a single continuous range is easier to audit when the data layout allows it.
Summing visible rows and filtered data
Options 1, 3, 5, and 7 tell AGGREGATE to ignore hidden rows in a reference. For example, if B2:B5 contains 100, 200, 300, and 400, and row 3 is manually hidden, the formula =AGGREGATE(9,7,B2:B5) returns 800, assuming there are no errors. The visible values are 100, 300, and 400.
Filtered-out rows are excluded by AGGREGATE regardless of the option number. That means option 6 can still produce a total that responds to an applied filter, even though option 6 does not exclude rows hidden manually. This distinction matters: a row hidden with the row-hide command and a row removed from view by a filter are not treated identically by every option.
Use option 7 when the desired total should omit errors and manually hidden rows, or option 5 when hidden rows should be omitted but errors should remain visible as a problem. If you need a total of every record whether it is visible or not, do not select an option that ignores hidden rows. Check the result after changing filters or hiding rows to confirm it matches the reporting requirement.
AGGREGATE compared with SUM and SUBTOTAL
SUM is the simplest function when all the referenced cells are valid numeric data. With values in B2:B10, =SUM(B2:B10) returns their total. It is easy to read and widely understood, but it does not offer AGGREGATE’s controls for ignoring errors or hidden rows.
SUBTOTAL is often used for totals that should respond to filters. Its function number 9 means SUM, while 109 means SUM while also ignoring manually hidden rows. With values in B2:B10, =SUBTOTAL(109,B2:B10) sums visible rows, including the behavior for filtered rows. SUBTOTAL excludes other SUBTOTAL results in its reference to help prevent double-counting, but it does not provide AGGREGATE’s option to ignore errors.
In practice, use SUM for a plain total, SUBTOTAL when filter-aware totals are the main need, and AGGREGATE when you want to choose exclusions such as errors or manually hidden rows. For example, a filtered report with error values may call for AGGREGATE with option 6 or 7, depending on whether manually hidden rows should also be left out.
Common problems and checks
If AGGREGATE returns an unexpected result, first check the function number and option number. Using 9 requests SUM; using the wrong function number performs another calculation. Then confirm that the reference covers the intended cells and that the option does not exclude rows you expected to count.
- A total is lower than expected: Look for error cells, hidden rows, or filtered-out records that the selected option omits.
- A result is still an error: Confirm that the formula uses an option that ignores errors, such as 2, 3, 6, or 7. Also verify that the error is within the referenced data and not coming from another part of the formula.
- Text figures are not added: Check whether the cells contain numbers stored as text. AGGREGATE is a calculation function, not a general data-cleaning tool.
- The result changes after filtering: That is expected for filtered rows. Decide whether the report should show the visible subset or the full dataset.
For dependable reporting, keep the source data intact and label the total clearly. A label such as Sum excluding errors and hidden rows conveys more than Total, particularly when colleagues may assume every record is included. You can also count or inspect errors in a separate check rather than treating their exclusion as proof that the data is clean.
When another formula is a better fit
AGGREGATE is useful for exclusions, but it is not a direct replacement for every summing function. It does not provide criteria arguments for conditions such as adding only rows for a particular region or month. If the values are clean and the task is a conditional total, SUMIF or SUMIFS is generally a clearer choice. For example, SUMIFS can select rows based on one or more criteria; it does not make the error-handling options of AGGREGATE unnecessary when the sum range itself may contain errors.
Likewise, if errors should be corrected rather than omitted, fix the formulas or clean the imported data at its source. An AGGREGATE total can be a useful summary, but it does not explain which records were excluded. If auditability matters, make excluded rows or errors visible through a separate review step.
Frequently Asked Questions
What number do I use in AGGREGATE for SUM?
Use 9 as the function number. The option number that follows it determines whether Excel ignores errors, hidden rows, or nested subtotal results.
Which AGGREGATE option ignores errors?
Options 2, 3, 6, and 7 ignore errors. Options 3 and 7 also ignore hidden rows; options 2 and 3 ignore nested SUBTOTAL and AGGREGATE results as well.
Does AGGREGATE ignore filtered rows?
Yes. AGGREGATE excludes rows hidden by a filter regardless of its option setting. To also ignore rows hidden manually, choose an option that includes that behavior, such as 5 or 7.
Why does AGGREGATE return a different total from SUM?
AGGREGATE may omit errors or hidden rows according to its option, while SUM does not offer those exclusions. Compare the selected range, filters, hidden rows, and error cells to find the reason.
Can AGGREGATE sum based on a condition?
It has no criteria argument like SUMIF or SUMIFS. Use a criteria-based function when that is the main task, and separately address errors in the data if they could affect the result.
Related
- Excel SUM Visible Rows: Add Filtered Data Correctly
- Excel Sum Text? What SUM Does With Text Cells
- Excel Sum Table Column: How to Total Values Correctly
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Track Changes in Word: How to Turn It On and Use ItTo turn on Track Changes in Microsoft Word, open the document and select Review > Track Changes >…
- How to Double Space in Word for the WebTo double-space text in Word for the web, select the text, open Home > Line and Paragraph Spacing,…
- Detect Duplicates in Google Sheets: A Practical GuideTo detect duplicates in Google Sheets, select the cells you want to check, choose Format > Conditional formatting,…
- Outlook Share Calendar Greyed Out? Causes and FixesIf Outlook’s Share Calendar button is greyed out, the calendar or account may not support sharing, or your…
- Combine Cells in Excel for a Title: Step-by-StepTo combine cells for a title in Excel for Microsoft 365 on Windows, enter the title in the…
- Excel Not Blank Conditional Formatting: How to Set It UpTo apply conditional formatting to nonblank cells in Excel for Microsoft 365 on Windows, select the range, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.