I’ve always found it frustrating that Excel’s most popular functions, such as SUM, AVERAGE, MIN, and MAX, fall apart the moment your data has a single error value. A stray #DIV/0! or #VALUE! in your range will cause the whole formula to return an error. The usual fix is wrapping everything in IFERROR, which works but clutters the formula bar.

AGGREGATE is a function I’ve leaned on precisely because it skips that mess. It handles errors, hidden rows, and filtered data natively, without the need for any nested workaround. If you work with real-world data that isn’t always clean, AGGREGATE is worth learning.

AGGREGATE does what other functions can’t

Here’s how the syntax works

Most people default to IFERROR when their formulas break. It works, but it’s a band-aid since you’re wrapping every formula in extra logic just to handle bad data. AGGREGATE eliminates that because error handling is built into the function itself.

It uses the following syntax:

=AGGREGATE(function_num, options, ref1, [ref2], …)

Here’s what each argument does:

• function_num: A number from 1 to 19 that tells AGGREGATE which calculation to perform. For example, 1 is AVERAGE, 4 is MAX, 5 is MIN, and 9 is SUM.
• options: A number from 0 to 7 that defines what AGGREGATE should ignore. The most useful ones are 5 (ignore hidden rows), 6 (ignore error values), and 7 (ignore both).
• ref1: The cell range you want to calculate.

So instead of =IFERROR(SUM(F1:F10),""), you’d write =AGGREGATE(9, 6, F1:F10). Same result, except AGGREGATE skips errors on its own, so you don’t need nesting or extra formulas. It’s cleaner and far easier to maintain.

It also handles hidden and filtered rows correctly

Your calculations stay accurate even when rows are out of sight

I prefer AGGREGATE also over the SUBTOTAL function in Excel. In the example sales spreadsheet, when you filter the Sales Amount column in a dataset — say, to show some rows — SUM still totals every row, including the ones you’ve hidden. SUBTOTAL handles filtered rows fine, but it can’t ignore errors at the same time. AGGREGATE does both.

Using the sales data as an example, if you filter the rows and use =AGGREGATE(9, 5, F2:F10), the formula sums only the visible Sales Amount values. Option 5 tells AGGREGATE to skip hidden rows. If your data also has error values scattered in the range, switching to option 7 ignores both hidden rows and errors in one go.

This usually matters in shared workbooks where different people apply different filters. A regular SUM gives them the wrong total the moment a filter is active, and they might not even notice. However, AGGREGATE adjusts automatically, and the result always reflects what’s actually on screen.

AGGREGATE only works with vertical ranges. It doesn’t support horizontal ranges or 3D references across multiple sheets, so keep that in mind when structuring your data.

AGGREGATE can replace 19 different functions

And a few of them are very useful

AGGREGATE isn’t just a better SUM. It supports 19 built-in functions, each assigned a number: 1 for AVERAGE, 2 for COUNT, 4 for MAX, 5 for MIN, 9 for SUM, and so on. But the ones I find most practical beyond the basics are LARGE (14), SMALL (15), and MEDIAN (12) — functions that are especially prone to breaking when your data has gaps or errors.

Some people don’t realize AGGREGATE can handle ranking functions. That’s where it stands apart from SUBTOTAL, which only covers 11 functions and doesn’t include LARGE, SMALL, or PERCENTILE.

Finding top and bottom values in messy data

Say you want to find the highest Sales Amount from the spreadsheet. Normally, you’d use =LARGE(F1:F10, 1). But if even one cell in that range contains an error, the formula fails.

With AGGREGATE, the formula becomes =AGGREGATE(14, 6, F1:F10, 1). Function 14 tells it to use LARGE; option 6 skips error values; and the last argument specifies the rank. In this dataset, the formula returns 3,750, which is the highest sale, from John Smith’s Electronics entry in the North region.

The same logic applies to SMALL. To pull the lowest Sales Amount, use =AGGREGATE(15, 6, F1:F10, 1), which returns 1,800 — Mike Wilson’s Home & Garden sale from the East region. Neither formula requires you to clean the data first.

Everyday scenarios where AGGREGATE pays off

I lean on AGGREGATE most when working with imported data. Files pulled from external sources or CSVs almost always have stray errors. Therefore, a mismatched data type here, a broken reference there. Rather than scrubbing the range before every calculation, AGGREGATE lets me get accurate totals, averages, and rankings.

It’s also handy in filtered summary rows. If you’re building a report where others will apply their own filters, placing AGGREGATE formulas in the summary ensures the numbers always match what’s visible. A regular AVERAGE won’t do that. It’ll include hidden rows, which will give a misleading result.

You don’t need to memorize all 19 function numbers. When you type =AGGREGATE( in a cell, Excel displays a dropdown listing every supported function with its corresponding number.

AGGREGATE pairs well with other Excel functions

Try combining it with conditional logic for even more control

AGGREGATE covers a lot on its own, but it gets more interesting when you pair it with functions like IF, CHOOSE, or INDEX. For instance, you can nest AGGREGATE inside a conditional formula to sum only visible rows that meet a specific criterion, something that would otherwise require a multi-step array formula.

It’s also worth exploring AGGREGATE’s array form, which accepts array expressions rather than cell references and enables more advanced filtering. If you rely on Excel for data analysis, it’s a function that simplifies a lot of the heavy lifting.