AGGREGATE

AGGREGATE performs functions like SUM, AVERAGE, MAX, MIN, COUNT, and 14 others using a numeric function code, and lets you specify what to ignore via an option code — including hidden rows, error values, and other SUBTOTAL or AGGREGATE functions. This makes it uniquely suited for summarising filtered lists where errors or hidden rows would otherwise skew results.

Excel

=AGGREGATE(9,5,B2:B5)

Google Sheets

=AGGREGATE(9,5,B2:B5)

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Cell row 1 column 1	Cell row 1 column 2
Cell row 2 column 1	Cell row 2 column 2
Cell row 3 column 1	Cell row 3 column 2
Cell row 4 column 1	Cell row 4 column 2

Formula

Runs server-side · free · no signup

How it works

19 is the function number — 9 means SUM (see the AGGREGATE function list for all 19 codes).
25 is the option number — 5 means ignore hidden rows; use 6 to also ignore errors.
3B2:B5 is the range to aggregate — only visible, non-error cells are summed.

Need a version for your data?

Try: “Sum a filtered list while ignoring hidden rows and error values”

SUMIF SUMIFS COUNTIF

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: May 28, 2026

AGGREGATE

Try it with your data

How it works

Related