We all start with SUM. It’s simple and reliable—until your data gets messy. Once you begin filtering or hiding rows, SUM no longer reflects what you see. That’s when SUBTOTAL helps. It can ignore filtered rows and avoid double-counting, and with the right option, it can skip hidden rows too. But it still fails if any cell contains an error… and that’s where this grown-up Excel function comes in.

What does AGGREGATE do

that the others can’t?

Allow me to demonstrate. I’ve got a nice-looking spreadsheet containing the sales and revenue for a product across several regions. Your typical Excel workbook. The goal is to calculate the total revenue. There are several ways to do that, as we’ve just discussed, but this table makes the differences between them especially clear.

In the first example, it’s an ordinary, clean table. All the values are visible, nothing is filtered, and there are no errors. SUM, SUBTOTAL, and AGGREGATE all return the same result. SUM wins this round simply because it has the easiest syntax:=SUM(F11:F60) There’s no reason to use a more complicated function when the data is clean and you only need a straightforward total. Now, suppose I manually hide some of the rows. SUM will still include them because it doesn’t care whether a row is visible. SUBTOTAL using function number 9 will also include them. It ignores filtered-out rows, but not rows you manually hide.

SUBTOTAL using 109, however, excludes the hidden rows with =SUBTOTAL(109,F11:F60). AGGREGATE can do the same thing. Both now show the sum of only the rows visible on the screen. It’s worth noting that SUBTOTAL with function number 9 does ignore rows removed by a filter. So, if I unhide everything and filter the table instead, both SUBTOTAL formulas return the visible total. SUM is now the only function still showing the larger value.

So far, SUBTOTAL is holding its own. But now we reach the ultimate test: what happens if one of the formulas in the revenue column breaks? Perhaps one cell tries to divide a value by zero and returns a #DIV/0! error. SUM breaks. SUBTOTAL breaks. Instead of a total, both formulas return the same error. AGGREGATE survives. It can ignore the broken cell, continue calculating the rest of the range, and still return a useful result. That’s what I’m talking about.

Anatomy of an AGGREGATE formula

No syntax pain, no gain

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

AGGREGATE’s syntax looks like above. The first argument, function_num, tells Excel what calculation you want to perform. It uses a number from 1 to 19. These include familiar operations such as AVERAGE, COUNT, MAX, MIN, SUM, LARGE, SMALL, and several statistical functions. The second argument, options, tells Excel what it should ignore.

After that, you provide the cell range or ranges you want the function to process. For the revenue example, I’m using function number 9, which represents SUM, and option 7, which ignores hidden rows and error values:

=AGGREGATE(9,7,F11:F60)

Read from right to left, the formula says: add the values in F11 through F60, but ignore any errors and any rows I’ve hidden. That’s the main appeal of AGGREGATE. It combines the calculation and the cleanup rules in one formula.

Of course, AGGREGATE isn’t limited to sums, either. Function number 1 calculates an average, 4 returns the largest value, and 5 returns the smallest. Some functions require an extra argument. For example: =AGGREGATE(14,6,F11:F60,3).

The Google Sheets workaround

You can still AGGREGATE

Google Sheets doesn’t have AGGREGATE. You can recreate parts of its behavior, but you’ll need to combine several functions. To sum a range while ignoring errors, you can use=SUM(IFERROR(F11:F60,0)). IFERROR replaces any errors with zero before SUM processes the range. It’s simple and works well when errors are the only problem.You can also filter the range so that only numeric values remain=SUM(FILTER(F11:F60,ISNUMBER(F11:F60))). FILTER creates a temporary list containing only valid numbers, and SUM adds them together. Errors, blank cells, and text are excluded.

Replicating AGGREGATE’s ability to ignore both errors and hidden or filtered rows is more complicated.One possible formula is=SUM(FILTER(IFERROR(F11:F60,0),SUBTOTAL(103,OFFSET(F11,ROW(F11:F60)-ROW(F11),0)))).Here, SUBTOTAL checks whether each row is visible, FILTER keeps the visible values, IFERROR replaces broken values with zero, and SUM calculates the final total. It works, but it also demonstrates why AGGREGATE is so useful. In Google Sheets, you need a small committee of functions to imitate what Excel handles with one.

AGGREGATE is for spreadsheets that have seen some things

SUM is still the right choice for clean, uncomplicated data. There’s no reason to summon AGGREGATE every time you need to add three cells together.

But real spreadsheets rarely stay clean. Rows get hidden. Filters get applied. Formulas break. Someone pastes text into a number column. Someone else adds another total halfway through the range. That’s when AGGREGATE earns its place. Its numbered arguments take a little time to remember, and it isn’t nearly as approachable as SUM. Still, once a workbook becomes complicated enough for SUM to mislead you and SUBTOTAL to collapse, AGGREGATE keeps returning something useful.