Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
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
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.