How to How to Use AGGREGATE to Ignore Errors in Excel - Practical Guide

Learn how to use the AGGREGATE function to perform calculations while automatically ignoring errors, empty cells, and hidden rows. This powerful function is essential for cleaning data and creating reliable formulas that won't break when errors appear in your dataset.

Why This Matters

AGGREGATE prevents formula failures caused by #DIV/0!, #VALUE!, or other errors in your data range. It's critical for robust reporting and analysis when working with imperfect datasets.

Prerequisites

•Basic understanding of Excel functions (SUM, AVERAGE, COUNT)
•Familiarity with function syntax and cell references
•Knowledge of error types (#DIV/0!, #VALUE!, #N/A)

Step-by-Step Instructions

Open your spreadsheet with error-prone data

Open Excel and create or load a dataset containing formulas that might generate errors (e.g., division by zero, lookup mismatches).

Click on the cell where you want the AGGREGATE result

Select the target cell where your cleaned calculation result should appear (e.g., cell D10 for a summary).

Type the AGGREGATE formula with function number and options

Enter: =AGGREGATE(function_num, option, range). Example: =AGGREGATE(9, 6, A1:A100) calculates SUM (function 9) while ignoring hidden rows and errors (option 6).

Select the appropriate function number

Choose function number (1=AVERAGE, 2=COUNT, 9=SUM, 15=SMALL, 16=LARGE) based on your calculation need.

Press Enter and verify the result ignores errors

Confirm the formula executes without breaking and correctly excludes error cells from the calculation.

Alternative Methods

IFERROR wrapping

Wrap individual formulas with IFERROR to handle errors cell-by-cell, but this is less efficient than AGGREGATE for large ranges.

SUMPRODUCT with ISNUMBER

Use SUMPRODUCT(ISNUMBER(range)*range) to sum only numeric values, but AGGREGATE is simpler and more versatile.

Tips & Tricks

✓Option 6 ignores both hidden rows and errors—use it for the most robust calculations.
✓Function number 9 (SUM) is most popular, but test all 19 available functions for your specific need.
✓AGGREGATE works in both Excel desktop and Excel Online.

Pro Tips

★Chain AGGREGATE functions to perform multi-step calculations: =AGGREGATE(1,6,AGGREGATE(9,6,range)).
★Use option 5 (ignore hidden rows only) when errors are intentional placeholders you want to include.
★Copy AGGREGATE formulas down columns efficiently using relative references (e.g., =AGGREGATE(9,6,A1:A10)).

Troubleshooting

Formula returns #NUM! or #VALUE! error

Verify your function number is between 1-19 and your option is 0-7. Check for circular references in your range.

AGGREGATE seems to include errors it shouldn't

Ensure your option parameter is correct (option 6 ignores errors + hidden rows; option 5 ignores hidden rows only).

Formula works but returns unexpected result

Test with a simpler range first (e.g., A1:A10) to isolate the issue before expanding to larger datasets.

Related Excel Formulas

IFERROR SUMPRODUCT SUMIF AVERAGEIF COUNTIF SMALL LARGE

Frequently Asked Questions

What's the difference between AGGREGATE option 5 and option 6?

Option 5 ignores hidden rows only. Option 6 ignores both hidden rows and error values (#DIV/0!, #VALUE!, etc.). Choose option 6 when your data contains calculation errors you want to skip.

Can AGGREGATE replace SUMIF for conditional sums?

No. AGGREGATE is for ignoring errors and hidden data, not conditional logic. Use SUMIF for conditional sums and AGGREGATE in combination with other functions if needed.

Does AGGREGATE work with array formulas?

Yes, AGGREGATE works with array formulas and is often more efficient. In Excel 365, it automatically processes arrays without Ctrl+Shift+Enter.