In Excel: use =AGGREGATE(9, 6, range) — it runs one of 19 calculations picked by function_num (9 is SUM, 14 LARGE, 15 SMALL), and options decides what is ignored: 6 skips error values, 5 hidden rows, 7 both, which a plain SUM cannot do.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
function_num | required | Which of 19 calculations to run: 9 sums, 1 averages, 14 is LARGE, 15 is SMALL. |
options | required | What to ignore: 6 skips error values, 5 skips hidden rows, 7 skips both. |
ref1 | required | The range to aggregate, or for LARGE/SMALL the range followed by a k argument. |
Related functions
Select the result cell and type =AGGREGATE(.
Enter the function number — 9 to sum, 1 to average, 14 for LARGE, 15 for SMALL.
Add a comma and the options number: 6 to ignore errors, 5 for hidden rows, 7 for both.
Add a comma and the range. For LARGE or SMALL, add one more comma and the k you want.
Need it as an auditable file?
Ships inside the linked template — formula-driven, unlocked, audit-ready.
What this does
AGGREGATE is SUBTOTAL with two upgrades: it offers 19 calculations instead of 11, and a second argument that says what to ignore. Option 6 skips error values, which is the reason most people find it — it totals a column that contains a stray #N/A without the column-wide IFERROR wrapper that would otherwise be needed. Option 5 skips hidden rows, and 7 skips both. Function numbers 14 and 15 give LARGE and SMALL, so "the third-largest value, ignoring errors" is a single call. It requires Excel 2010 or later. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “excel aggregate function” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected formula shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
A column with a stray #N/A in it still totals correctly with =AGGREGATE(9, 6, D2:D500). The third-largest deal size ignoring errors: =AGGREGATE(14, 6, D2:D500, 3). The smallest visible value in a filtered list: =AGGREGATE(15, 5, D2:D500, 1). AGGREGATE is the one aggregation that survives dirty data, which is why it belongs on any summary sitting on top of a live lookup. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. Treat “excel aggregate function” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
Common mistakes
- Omitting the options argument — unlike
SUBTOTALit is required, and leaving it out is an error rather than a default. - Forgetting the extra k argument for functions 14 to 19, which need it and error without it.
- Using
AGGREGATEto paper over errors that should be fixed; ignoring an#N/Ais right when the data is genuinely incomplete, wrong when a lookup is broken.
Frequently asked questions
How do I sum a column that contains errors?
=AGGREGATE(9, 6, range). Option 6 tells it to skip error values, so no IFERROR wrapper is needed on every row.
What is the difference between AGGREGATE and SUBTOTAL?
AGGREGATE offers 19 calculations to SUBTOTAL's 11 and can ignore errors, which SUBTOTAL cannot. SUBTOTAL is simpler when filtering is all you need.
How do I get the nth largest value ignoring errors?
=AGGREGATE(14, 6, range, n) — function 14 is LARGE and the final argument is the rank you want.