Skip to content
Excel functionNot in Google Sheets

AGGREGATE function in Google Sheets

Google Sheets doesn't have AGGREGATE — here's what does.

SUBTOTAL's 100-series codes cover the hidden-row behavior; error-skipping needs an IFERROR wrap.

What AGGREGATE does in Excel

Returns an aggregate in a list or database

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

Convert AGGREGATE formulas to Google Sheets

Before and after, formula by formula.

  1. Sum while ignoring hidden rows:

    Excel
    =AGGREGATE(9, 5, B2:B100)
    Google Sheets
    =SUBTOTAL(109, B2:B100)

    SUBTOTAL codes 101–111 skip rows hidden by hand; plain 1–11 only skip filtered-out rows.

  2. Sum while ignoring error cells:

    Excel
    =AGGREGATE(9, 6, B2:B100)
    Google Sheets
    =SUM(ARRAYFORMULA(IFERROR(B2:B100, 0)))

Converting more than one formula? The Excel ↔ Sheets translator rewrites whole workbooks and flags every AGGREGATE so none slip through silently.

Browse Excel math functions

More conversions to Google Sheets