Quick Navigation

AGGREGATE Function

Excel 2010+

Summary

The Excel AGGREGATE function performs calculations like AVERAGE, SUM, MAX, MIN and more on data ranges while intelligently ignoring hidden rows, error values, and nested SUBTOTAL/AGGREGATE functions based on specified options.

Syntax

AGGREGATE(function_num, options, ref1, [ref2], …)

Parameters

Parameter	Type	Required	Description
function_num	`Number`	Yes	Number 1-19 selecting aggregation function
options	`Number`	Yes	0-7 determining ignore behavior
ref1	`Range`	Yes	Primary data range
ref2	`Range`	No	Optional second argument for specific functions

Using the AGGREGATE Function

AGGREGATE excels in scenarios with filtered data, tables with hidden rows, or datasets containing errors. Unlike traditional functions, it can exclude problematic values while maintaining calculation accuracy across dynamic ranges.

Common AGGREGATE Examples

Maximum ignoring errors

=AGGREGATE(4,6,A1:A20)

Finds MAX value ignoring error values (option 6)

Average ignoring hidden rows

=AGGREGATE(1,5,B2:B100)

Calculates AVERAGE excluding manually hidden rows (option 5)

3rd largest value

=AGGREGATE(14,3,A1:A50,3)

Returns 3rd largest number ignoring hidden rows and errors

Sum ignoring nested subtotals

=AGGREGATE(9,3,C1:C200)

SUM excluding hidden rows, errors, and nested AGGREGATE/SUBTOTAL

Frequently Asked Questions

14 corresponds to LARGE function - returns kth largest value

Option 3 ignores hidden rows, errors, AND nested SUBTOTAL/AGGREGATE functions

Yes, perfect for Excel Tables as it respects filter states automatically

Common Errors and Solutions

#VALUE! error

Cause: Missing ref2 argument for functions requiring it (LARGE, SMALL, etc.)

Solution: Provide kth position argument: =AGGREGATE(14,3,A1:A10,2)

#VALUE! with 3D references

Cause: AGGREGATE doesn't support 3D references across worksheets

Solution: Use single worksheet ranges only

Wrong results with calculations

Cause: Array syntax with formulas bypasses ignore options

Solution: Use simple range references

Notes

Designed for vertical data columns, not horizontal rows
IntelliSense shows function_num dropdown when typing
Option 0/omitted only ignores nested SUBTOTAL/AGGREGATE
Best replacement for SUBTOTAL in modern Excel
Function numbers 14-19 need second [k] parameter

Compatibility

Available in: Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in: Excel 2007 and earlier

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2010+