AGGREGATE Function in Excel: SUBTOTAL That Can Ignore Errors

⏱ 1 min readUpdated 28 September 2026

MathLevel: ExpertAvailable in: Excel 2010+

In this article
  1. Syntax
  2. Examples
  3. Example 1
  4. Example 2
  5. Related functions

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.

SUBTOTAL · IFERROR · LARGE

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

Keep reading

Excel Functions A–ZXIRR Function in Excel: Real Return on SIPs and Irregular Cash FlowsExcel Functions A–ZVLOOKUP Function in Excel: Look Up a Value in the First Column of a TableExcel Functions A–ZDATE Function in Excel: Build a Date From Year, Month and Day
✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong