Excel AGGREGATE Formula: Two Practical Examples Explained

Formulas & functions · Updated November 18, 2026

Use Excel’s AGGREGATE function to calculate a result while choosing whether to ignore errors, hidden rows, or both. For example, =AGGREGATE(9,6,A2:A8) adds the numbers in A2:A8 while ignoring error values. The steps below show how to set up that example and a second one that finds the largest value in a range while ignoring hidden rows.

AGGREGATE is useful when a normal SUM, MAX, or similar formula would be disrupted by errors or rows you do not want included. Before starting, identify the cells you want to calculate and decide what to exclude. The examples use Excel for Microsoft 365 on Windows; AGGREGATE is also available in Excel 2010 and later.

What You’ll Need

  • An Excel workbook with a vertical list of numbers, or a worksheet where you can enter the sample data.
  • A blank cell for the result. Keep it outside the range being calculated so it is not accidentally included.
  • For the first example, a range containing at least one error value, such as #N/A or #DIV/0!.
  • For the second example, a range with a row you can hide. You can use sample values if you do not want to change your own data.

Step-by-Step

  1. Understand the formula’s parts. AGGREGATE takes a function number, an option number, and a reference or array, with an additional argument for some calculations. The function number tells Excel what to calculate: 1 is AVERAGE, 4 is MAX, 9 is SUM, 12 is MEDIAN, and 14 is LARGE. The option number tells Excel what to ignore. For example, 5 ignores hidden rows, 6 ignores error values, and 7 ignores both hidden rows and errors. The argument choices depend on the function number, so check the order carefully when building a formula.
  2. Prepare the error-handling example. Enter numbers down A2:A8, with two cells containing errors. For instance, use 12 in A2, 18 in A3, #N/A in A4, 25 in A5, 30 in A6, #DIV/0! in A7, and 15 in A8. In a real workbook, those errors might come from a lookup or division formula. You do not need to create errors deliberately if your own data already has them. Choose an empty cell such as B2 for the total.
  3. Enter a sum that skips errors. With the sample values and errors in A2:A8, enter =AGGREGATE(9,6,A2:A8) in B2 and press Enter. The 9 selects SUM, and option 6 tells AGGREGATE to ignore error values. The valid numbers add up to 12 + 18 + 25 + 30 + 15, so B2 should show 100. The error cells remain unchanged; AGGREGATE only leaves them out of this calculation.
  4. Check that the result responds to changes. If you replace one error in A4 with 10, the same formula should return 110. If you restore the error, it should return 100 again. This is a useful check that the formula points to the intended range and is excluding errors rather than hiding or deleting data. You can also copy the formula to another result cell, but review its range references before relying on the copied result.
  5. Set up the hidden-row example. In B2:B7, enter 8, 11, 6, 14, 9, and 12, one value per row. Put the result formula somewhere outside B2:B7, such as D2. Then hide the row containing 14: right-click the row number at the left edge of the worksheet and choose Hide. Hiding a row changes its visibility, not the value in the cell.
  6. Find the largest visible value. With the numbers in B2:B7 and the row containing 14 hidden, enter =AGGREGATE(4,5,B2:B7) in D2. Function number 4 selects MAX, and option 5 tells Excel to ignore hidden rows. The remaining visible values are 8, 11, 6, 9, and 12, so the result should be 12. If you unhide the row containing 14, the result should change to 14.
  7. Choose the option that matches your goal. Use option 6 if errors should be excluded but hidden rows should still count. Use option 5 if hidden rows should be excluded but errors should remain capable of affecting the result. Use option 7 when you want to ignore both hidden rows and errors. For instance, if you want a sum that ignores both, use the same range layout as the first example and enter =AGGREGATE(9,7,A2:A8). With the original sample, the result is 100; the option would also exclude any hidden rows in that range.
  8. Use the function number for the calculation you need. You can replace 9 with another supported function number while keeping an option that suits the data. With errors in A2:A8, =AGGREGATE(1,6,A2:A8) calculates the average of the five valid values, which is 20. With the values in B2:B7 and the row containing 14 hidden, =AGGREGATE(4,5,B2:B7) finds the largest visible value, 12. Be aware that not every function number accepts the same argument pattern; for example, LARGE uses an additional k argument.
  9. Confirm the range and save the workbook. Click the result cell and inspect the formula bar to confirm that the range includes the intended data and excludes the result cell. If you used the hiding example, unhide the row when you finish if the worksheet should return to its original appearance. Save the workbook so the formulas and any intended row visibility are retained.

Common Mistakes

Mixing up function and option numbers: The first number says what to calculate; the second says what to ignore. In =AGGREGATE(9,6,A2:A8), 9 means SUM and 6 means ignore errors. Reversing them changes the meaning and may produce an error or an unexpected result.

Expecting option 6 to ignore hidden rows: Option 6 ignores errors, not hidden rows. If both types of values should be skipped, use option 7. If only hidden rows should be skipped, use option 5. Check the option before assuming a row is excluded from the calculation.

Including the result cell in the calculation range: If the formula is inside its own input range, Excel may report a circular reference. Place the answer outside the data range, or revise the range so it ends before the result cell.

Assuming the function repairs errors: AGGREGATE can omit certain errors from its calculation, but it does not fix their causes or remove them from the worksheet. Other formulas that refer to those cells may still show errors. Use error-handling functions separately if you need to change how errors appear elsewhere.

Using a horizontal range without checking the result: AGGREGATE is intended for vertical ranges. If your data runs across a row, test the formula carefully or arrange the data vertically. Hidden-row behavior in particular is designed around rows of data, not columns hidden from view.

Tips

Keep the data range as narrow as practical. Referring to the actual data cells makes formulas easier to audit than selecting entire columns, and it helps prevent headings or unrelated values from being included. If you add records often, consider converting the list into an Excel table and checking that your formula continues to cover new entries.

Write down what each number means when a formula is likely to be maintained by someone else. A short note beside a result, such as “sum, ignore errors,” can make a formula like =AGGREGATE(9,6,A2:A8) much easier to understand later. You can also use Excel’s Insert Function button next to the formula bar to inspect function arguments, though typing the formula directly is often quicker.

Test the formula with a known result before using it for an important report. For the first example, add the five valid numbers by hand and compare the total with 100. For the second, check that the maximum changes from 12 to 14 when you unhide the row. These checks help reveal the wrong range or option number. Remember that hiding a row is a display change; it does not delete the data or protect it from being viewed.

If you share the workbook with people using different regional settings, their Excel may use semicolons rather than commas between function arguments. The comma versions shown here are standard Excel syntax for the stated setup. Excel’s regional settings determine which separator is accepted on a particular computer, so adjust separators if Excel flags the punctuation rather than the function itself.

Troubleshooting

The formula returns #NAME?: Check that AGGREGATE is spelled correctly and that you are using Excel 2010 or later. Earlier Excel versions do not include this function. If a workbook is opened in an older version, use a compatible alternative or open it in a supported version of Excel.

The result still includes a hidden value: Confirm that the row itself is hidden and that you selected an option that excludes hidden rows, such as 5 or 7. Option 6 does not exclude hidden rows. Also check that the data is arranged vertically and that the hidden row is actually inside the formula’s range.

An error still appears in the result: Make sure the error is within the referenced range and that the option is 6 or 7. Check for errors outside the range, and verify that the function number and argument order are correct. Some formula arrangements and nested calculations can affect which errors AGGREGATE can ignore; a direct cell-range reference is a good way to test the basic behavior.

Excel rejects the formula as you enter it: Check for a missing parenthesis, an incorrect argument separator, or a misspelled function name. The examples use commas. If your regional settings require semicolons, replace argument-separating commas with semicolons for your installation. Do not put quotation marks around a cell range.

The answer is unexpected or zero: Confirm that the cells contain numbers rather than numbers stored as text, and check that the selected range is correct. For a hidden-row calculation, unhide all rows temporarily and compare the result with the visible-only result. If the formula references the wrong cells, correct the range and recalculate.

Frequently Asked Questions

What does AGGREGATE do in Excel?
It applies a calculation such as SUM, AVERAGE, MAX, or MEDIAN to a range, with options to ignore certain hidden rows, errors, or nested SUBTOTAL and AGGREGATE results. The exact behavior depends on its function and option numbers.

What is the difference between AGGREGATE option 6 and option 7?
Option 6 ignores error values but does not exclude hidden rows. Option 7 ignores both error values and hidden rows. Choose based on what should contribute to your result.

Can AGGREGATE ignore rows hidden by a filter?
Options that ignore hidden rows are intended to exclude rows that are not visible, including rows hidden by filtering in a vertical list. Test the result after applying the filter, especially if your data layout or formula is more complex.

Is AGGREGATE available in Excel for Mac or Excel for the web?
AGGREGATE is available in current Microsoft 365 versions for Windows, Mac, and the web. The formula syntax is the same, although the location of commands and some interaction details can differ. On Mac, use the row controls to hide or unhide a row; the Windows right-click instruction may look different.

Can AGGREGATE ignore errors in every kind of formula?
No. Its error-ignoring options work in supported AGGREGATE calculations, but a complex array calculation or nested formula can behave differently from a direct range reference. Start by testing the function on a simple vertical cell range, then build up the more complex calculation if needed.

Why does Excel show #NAME? for AGGREGATE?
The workbook may be open in Excel 2007 or earlier, which does not support AGGREGATE, or the function may be misspelled. Open the file in Excel 2010 or later, or replace the formula with an alternative compatible with the older version.

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.