AGGREGATE function

The AGGREGATE function in Excel performs a variety of mathematical operations on a range of data, such as sum, average, or count, while offering additional options for ignoring certain types of data (e.g., errors or hidden rows).

Syntax

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

Parameters

• function_num: This is a required argument. It specifies the function to use. This is an integer from 1 to 19, where each number corresponds to a specific aggregation function.
  • For example:
    • 1 = AVERAGE
    • 2 = COUNT
    • 3 = COUNTA
    • 9 = SUM
    • 11 = STDEV.S
    • 19 = VAR.P
• options: This is a required argument. It determines which data types to ignore during the aggregation.
  • 0 = Ignore nothing (default behavior)
  • 1 = Ignore hidden rows
  • 2 = Ignore error values
  • 3 = Ignore hidden rows and error values
  • 4 = Ignore nothing (useful when you want to apply only the function and ignore nothing)
• array: This is the required range or array of data to aggregate. It can be a range of cells or an array constant.
• [k]: This is an optional argument. It is used for certain functions, such as PERCENTILE, QUARTILE, LARGE, and SMALL, where you need to specify which value to return (e.g., the nth largest or nth smallest value). If the function doesn’t require this, you can leave it blank.

How It Works

The AGGREGATE function combines a variety of operations, such as averages, sums, counts, and more, while allowing flexibility to ignore certain data types like errors or hidden rows. It’s useful when working with large datasets where you may need to exclude certain values (e.g., errors or hidden rows) without manually filtering them.

Examples

Example 1: Calculate the Sum Ignoring Errors

If the data in cells A1:A10 contains some errors (e.g., #DIV/0!), but you want to calculate the sum while ignoring errors:

=AGGREGATE(9, 2, A1:A10)

• 9 specifies the SUM function.
• 2 tells Excel to ignore errors.
• A1:A10 is the range to sum.

Example 2: Calculate the Average Ignoring Hidden Rows

If you have hidden rows in your dataset and you want to calculate the average while ignoring the hidden rows, use the following:

=AGGREGATE(1, 1, A1:A10)

• 1 specifies the AVERAGE function.
• 1 tells Excel to ignore hidden rows.

Example 3: Find the 3rd Largest Value in a Range Ignoring Errors

If you want to find the 3rd largest value in a range and ignore errors, use:

=AGGREGATE(14, 2, A1:A10, 3)

• 14 specifies the LARGE function (which returns the nth largest value).
• 2 tells Excel to ignore errors.
• 3 specifies that you want the 3rd largest value in the range.

Example 4: Count the Number of Non-Empty Cells in a Range Ignoring Errors

If you want to count the number of non-empty cells in a range but ignore errors, you can use:

=AGGREGATE(3, 2, A1:A10)

• 3 specifies the COUNTA function (counts non-empty cells).
• 2 tells Excel to ignore errors.
• A1:A10 is the range.

Key Points

• The AGGREGATE function is versatile and can perform a wide range of calculations (e.g., SUM, AVERAGE, COUNT) while giving you control over what data to ignore (errors, hidden rows).
• It’s more powerful than individual functions because it allows for excluding certain types of data.
• The function number (function_num) determines which operation to perform, while the options parameter (options) lets you control how to handle different types of data (errors, hidden rows).

Use Cases

• Data Analysis: When you need to calculate metrics on large datasets while ignoring certain data types (e.g., error values or hidden rows).
• Financial Modeling: Useful for calculations involving sums, averages, and counts, while excluding specific values from the analysis.