AGGREGATE Function in Excel: SUBTOTAL That Can Ignore Errors

📎 This article includes 1 downloadable practice file ↓

MathLevel: ExpertAvailable in: Excel 2010+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. AGGREGATE in practice
6. Where you will use it
7. Worked example
8. Mistakes people make
9. Related functions
10. Continue learning

AGGREGATE is like SUBTOTAL with 19 functions and extra options — most usefully, ignoring error values such as #N/A in the range.

Syntax

=AGGREGATE(function_num, options, ref1, [k])

Argument	What it means
function_num	9 = SUM, 1 = AVERAGE, 14 = LARGE, 15 = SMALL, and more.
options	6 = ignore errors, 5 = ignore hidden rows, 7 = both.
k	Needed for LARGE/SMALL-type functions.

Examples

Example 1

=AGGREGATE(9, 6, E2:E500)

Sum a column that contains some #N/A values.

Example 2

=AGGREGATE(14, 6, E2:E500, 1)

Largest value, ignoring errors.

AGGREGATE in practice

Where you will use it

• Sum a column that contains #N/A errors
• Largest value ignoring hidden rows
• Percentile ignoring errors

Worked example

H1:H4 holds 100, #N/A, 300, 400. Function 9 = SUM, 4 = MAX, 1 = AVERAGE; option 6 ignores the error.

Function	Formula	Result
9	=AGGREGATE(A2,6,$H$1:$H$4)	800
4	=AGGREGATE(A3,6,$H$1:$H$4)	400
1	=AGGREGATE(A4,6,$H$1:$H$4)	266.6666667

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Forgetting the option	6 = ignore errors, 5 = hidden rows, 7 = both
Array form	Functions 14-19 (LARGE, SMALL...) need the k argument

Related functions

SUBTOTAL · IFERROR · LARGE

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Math & Trig guides →

RANDARRAY Function in Excel: A Whole Grid of Random NumbersRANDARRAY returns an array of random numbers, optionally whole numbers between limits — instant…PERCENTOF Function in Excel: Share of the TotalPERCENTOF returns a subset’s share of a total — mostly used inside GROUPBY and…RAND Function in Excel: Random Decimal Between 0 and 1RAND returns a new random number between 0 and 1 on every recalculation —…RANDBETWEEN Function in Excel: Random Whole Numbers for Test DataRANDBETWEEN returns a random integer between two numbers. It recalculates on every change —…

📎 Practice files for this article

• 📗
  Practice workbook: Math & rounding24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.