Math & Trig

AGGREGATE Function in Excel

Returns an aggregate calculation with options to ignore errors and hidden rows.

Syntax

  • =AGGREGATE(function_num, options, ref1, [ref2], ...)

Arguments

  • function_num (required): Number 1-19 specifying function
  • options (required): What to ignore (0-7)
  • ref1 (required): First range

Examples

  • =AGGREGATE(9, 6, A1:A100) - Sum ignoring errors - Result: Sum value
  • =AGGREGATE(14, 6, A1:A100, 3) - 3rd largest ignoring errors - Result: 3rd largest

Excel AGGREGATE function documentation

  • AGGREGATE extends SUBTOTAL with more functions and options to ignore errors, nested subtotals, and hidden rows.
  • Syntax: =AGGREGATE(function_num, options, ref1, [ref2], …).
  • Available in Excel 2010+ — ideal for dashboards built on imperfect real-world data.
  • Related: [SUBTOTAL](/functions/subtotal/), [SUM](/functions/sum/), [AVERAGE](/functions/average/).

Options argument (how AGGREGATE behaves)

  • 0 — ignore nested SUBTOTAL and AGGREGATE cells in ref.
  • 1 — ignore hidden rows only.
  • 2 — ignore error values (#N/A, #DIV/0!, etc.).
  • 3 — ignore both hidden rows and errors.
  • 6 — ignore errors only (common for SUM on lookup columns with #N/A).
  • 7 — ignore errors and nested subtotals.

Step-by-step: SUM that skips #N/A errors

  • Step 1 — Lookup column F has mix of numbers and #N/A from [VLOOKUP](/functions/vlookup/).
  • Step 2 — =SUM(F:F) propagates errors; =AGGREGATE(9, 6, F2:F500) sums numbers only.
  • Step 3 — Compare with fixing root lookup cause — AGGREGATE is a display fix, not data fix.
  • Step 4 — For filtered lists add option 5 or 7 to also ignore hidden rows.
  • Step 5 — Document in the model that errors are excluded from the KPI tile.

AGGREGATE vs SUBTOTAL decision guide

  • SUBTOTAL — simpler; filtered reports without error values in range.
  • AGGREGATE — errors present, nested subtotals, or need MEDIAN/LARGE/etc. with options.
  • function_num 1–19 map to AVERAGE, COUNT, MAX, MEDIAN, SUM, and more — see Excel help.
  • Prefer fixing source errors over permanent AGGREGATE wrappers in audit-sensitive models.

Worked examples to copy

  • Sum ignore errors: =AGGREGATE(9, 6, B2:B1000).
  • Average visible only: =AGGREGATE(1, 5, C:C) — 1=AVERAGE, 5=hidden rows ignored.
  • Max ignore errors: =AGGREGATE(4, 6, range).
  • Second largest ignoring errors: =AGGREGATE(14, 6, range, 2) — 14=LARGE, k=2.

People also ask

  • AGGREGATE vs SUBTOTAL? — AGGREGATE adds error handling and more functions.
  • Which option ignores #N/A? — Option 2 or 6 depending on hidden-row needs.
  • AGGREGATE on full column? — Works but slow; bound the range to your Table.
  • Can AGGREGATE replace pivot totals? — Sometimes for lightweight dashboards; pivots for heavy analysis.

Common errors

  • Options: 5=ignore hidden, 6=ignore errors, 7=ignore both

Use cases

  • Error-tolerant calculations
  • Filtered aggregations
  • Robust statistics

Frequently asked questions

  • What are the function numbers for AGGREGATE? 1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV.S, 8=STDEV.P, 9=SUM, 10=VAR.S, 11=VAR.P, 12=MEDIAN, 13=MODE.SNGL, 14=LARGE, 15=SMALL, 16=PERCENTILE.INC, 17=QUARTILE.INC, 18=PERCENTILE.EXC, 19=QUARTILE.EXC.
  • How do I sum a range that contains errors? Use =AGGREGATE(9, 6, range) where 9=SUM and 6=ignore errors. This sums all numeric values while skipping #N/A, #VALUE!, #DIV/0!, etc. Much cleaner than wrapping each cell in IFERROR.
  • What is the difference between AGGREGATE and SUBTOTAL? AGGREGATE has 19 functions (vs 11), can ignore error values (SUBTOTAL cannot), and includes LARGE, SMALL, MEDIAN, PERCENTILE. Use AGGREGATE when you need error handling or functions not in SUBTOTAL.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use AGGREGATE

  • Error-tolerant calculations — common Math & Trig scenario for AGGREGATE.
  • Filtered aggregations — common Math & Trig scenario for AGGREGATE.
  • Robust statistics — common Math & Trig scenario for AGGREGATE.

AGGREGATE in the Math & Trig category

  • Browse all Math & Trig functions at /categories/math-trig/ for related formulas.
  • AGGREGATE syntax: =AGGREGATE(function_num, options, ref1, [ref2], ...)
  • function_num (required): Number 1-19 specifying function
  • options (required): What to ignore (0-7)
  • ref1 (required): First range
  • Confirm AGGREGATE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm AGGREGATE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • SUBTOTAL (/functions/subtotal/): Returns a subtotal in a list or database, with options to include or exclude hidden values.
  • SUM (/functions/sum/): Adds all the numbers in a range of cells.
  • LARGE (/functions/large/): Returns the k-th largest value in a data set.
  • SMALL (/functions/small/): Returns the k-th smallest value in a data set.

Errors to watch for

  • Options: 5=ignore hidden, 6=ignore errors, 7=ignore both — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter AGGREGATE on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

AGGREGATE worked examples to copy

  • =AGGREGATE(9, 6, A1:A100) — Sum ignoring errors. Expected result: Sum value.
  • =AGGREGATE(14, 6, A1:A100, 3) — 3rd largest ignoring errors. Expected result: 3rd largest.

AGGREGATE reference summary for crawlers and offline review

  • AGGREGATE belongs to the Math & Trig category in Excel. Returns an aggregate calculation with options to ignore errors and hidden rows.
  • Full syntax: =AGGREGATE(function_num, options, ref1, [ref2], ...). Open /functions/aggregate/ for parameters, FAQs, and related pages.
  • Common mistakes: Options: 5=ignore hidden, 6=ignore errors, 7=ignore both
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.