How to Use the AGGREGATE Function in Microsoft Excel to Calculate Data While Ignoring Errors and Hidden Rows

In data-driven spreadsheets, formula errors such as #N/A, #VALUE!, #DIV/0!, and #REF! frequently crop up due to missing survey responses, external API lookups, or division calculations. The moment a single cell inside a data range contains an error, standard Excel functions like SUM, AVERAGE, MAX, or LARGE instantly break, displaying that same error across all dependent summary cards and dashboard metrics.

While wrapping every individual formula inside IFERROR or IFNA is a common workaround, it clutters worksheets and increases calculation overhead. Moreover, standard functions cannot ignore manually hidden or filtered rows. To solve both issues simultaneously, Microsoft introduced the powerful AGGREGATE function. AGGREGATE can execute 19 different mathematical operations while giving you total control to ignore calculation errors, hidden rows, and nested subtotal calculations. This tutorial explains how to use the AGGREGATE function in Microsoft Excel with practical, real-world examples.

---

AGGREGATE Function Syntax: Reference Form vs Array Form

Unlike most Excel functions, AGGREGATE features two distinct syntax forms depending on whether you are executing basic summary calculations or positional array rankings:

1. Reference Syntax Form (Functions 1–13)

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

Used for standard statistical calculations like Sum, Average, Count, Min, Max, and Standard Deviation across one or more cell ranges.

2. Array Syntax Form (Functions 14–19)

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

Used for advanced ranked array functions, such as LARGE, SMALL, PERCENTILE.INC, PERCENTILE.EXC, QUARTILE.INC, and QUARTILE.EXC, where the optional argument [k] specifies the rank position or percentile percentage.

---

Understanding Function Numbers (1 to 19)

The first argument, function_num, specifies which calculation to perform:

Function Num	Operation	Syntax Form
1	AVERAGE	Reference
2	COUNT (Numbers)	Reference
3	COUNTA (Non-blank)	Reference
4	MAX	Reference
5	MIN	Reference
6	PRODUCT	Reference
7	STDEV.S	Reference
8	STDEV.P	Reference
9	SUM	Reference
10	VAR.S	Reference
11	VAR.P	Reference
12	MEDIAN	Reference
13	MODE.SNGL	Reference
14	LARGE (Requires k)	Array
15	SMALL (Requires k)	Array
16	PERCENTILE.INC (Requires k)	Array
17	QUARTILE.INC (Requires k)	Array
18	PERCENTILE.EXC (Requires k)	Array
19	QUARTILE.EXC (Requires k)	Array

---

Understanding Options Codes (0 to 7)

The second argument, options, dictates what elements AGGREGATE will overlook during calculation:

• 0: Ignore nested SUBTOTAL and AGGREGATE functions.
• 1: Ignore hidden rows and nested SUBTOTAL/AGGREGATE functions.
• 2: Ignore error values and nested SUBTOTAL/AGGREGATE functions.
• 3: Ignore hidden rows, error values, and nested SUBTOTAL/AGGREGATE functions (Most Popular).
• 4: Ignore nothing.
• 5: Ignore only hidden rows.
• 6: Ignore only error values.
• 7: Ignore only hidden rows and error values.

---

Practical Example 1: Summing a Range Containing Error Values

Suppose column D2:D50 contains financial figures, but cells D12 and D35 contain #N/A errors from incomplete data imports. A standard =SUM(D2:D50) returns #N/A.

Using AGGREGATE with function code 9 (SUM) and option code 6 (ignore error values):

# Sum D2:D50 while ignoring all calculation errors
=AGGREGATE(9, 6, D2:D50)

The formula skips the errors and sums every valid numeric cell seamlessly.

---

Practical Example 2: Averaging Visible Rows While Ignoring Errors

If your dataset is filtered using an AutoFilter and also contains intermittent #DIV/0! errors, you can ignore both conditions simultaneously using option code 3:

# Calculate Average (1), ignoring hidden rows, error values, and nested subtotals (3)
=AGGREGATE(1, 3, E2:E100)

This single formula delivers bulletproof dynamic reporting: filtering the table recalculates the average for visible records only, without failing if an error is present.

---

Practical Example 3: Finding the Smallest Non-Zero Number with Errors

In the Array form (functions 14–19), AGGREGATE can perform conditional array manipulations without pressing Ctrl + Shift + Enter:

# Find the 2nd largest value in D2:D50, ignoring errors and hidden rows
=AGGREGATE(14, 3, D2:D50, 2)

---

AGGREGATE vs SUBTOTAL: Comparison Summary

• Error Tolerance: SUBTOTAL breaks whenever an error value is present in the target range. AGGREGATE effortlessly bypasses errors.
• Advanced Mathematics: SUBTOTAL is restricted to 11 basic arithmetic operations. AGGREGATE includes Median, Mode, Large, Small, and Percentile calculations.
• Array Support: Functions 14–19 in AGGREGATE can accept calculated arrays directly, enabling advanced multi-criteria ranking models.