AGGREGATE
Performs calculations while ignoring hidden rows or errors.
=AGGREGATE(function_num, options, array, [k])This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Performs calculations while ignoring hidden rows or errors.
AGGREGATE is an advanced multi-purpose function that evaluates lists and databases using one of 19 underlying operations (such as SUM, AVERAGE, or LARGE). Unlike the SUBTOTAL function, AGGREGATE provides 'options' to selectively ignore hidden rows, error values (#N/A, #VALUE!, #DIV/0!), and even nested SUBTOTAL or AGGREGATE results to prevent double-counting. It supports two syntax variations: Reference-form (for functions 1-13) and Array-form (for functions 14-19). A critical technical edge case is that AGGREGATE does not support 3D references across multiple worksheets. For statistical functions 14-19, the [k] argument must be provided or the function returns a #VALUE! error. Best practice involves using option 6 or 7 when dealing with data imported from external sources that may contain intermittent calculation errors to ensure report stability without manually cleaning data.
Quick reference
Syntax
=AGGREGATE(function_num, options, array, [k])Inputs
Arguments
Example: 9
Example: 6
Example: A2:A100
Formula patterns
Examples
sum visible cells and ignore errors
=AGGREGATE(9, 7, A2:A100)find 2nd largest value ignoring errors
=AGGREGATE(14, 6, B2:B50, 2)average visible rows while ignoring nested subtotals
=AGGREGATE(1, 0, C2:C100)Avoid these issues
Common Errors
#VALUE!
Cause: The function_num is between 14-19 but the mandatory [k] argument is missing.
Fix: Add the required rank or percentile value as the fourth argument (e.g., k=1 for the smallest value in SMALL).
#VALUE!
Cause: A 3D reference or a range from another worksheet is passed which the function cannot process.
Fix: Ensure all references are 2D and point to the current worksheet only.
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Performs calculations while ignoring hidden rows or errors.
um: Operation ID (9=SUM, 1=AVERAGE, etc.) options: Behavior (6=Ignore errors) array: Range to calculate
#VALUE!: Add the required rank or percentile value as the fourth argument (e.g., k=1 for the smallest value in SMALL). #VALUE!: Ensure all references are 2D and point to the current worksheet only.