Excel intermediate aggregate functions data-analysis

How to Use the AGGREGATE Function in Excel?

Using the AGGREGATE Function is a Excel function that the aggregate function performs calculations like sum, average, or count while ignoring specified types of data such as hidden rows or errors.. Formula Genius generates and validates this formula automatically from a plain-English prompt.

The AGGREGATE function allows you to perform calculations while ignoring hidden rows and error values, making data analysis more efficient.

Generate This Formula Browse Library

The Formula

Prompt

"Sum, average, or count a range while ignoring hidden rows, error values, or both using AGGREGATE instead of SUM or AVERAGE"

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

The AGGREGATE function performs calculations like sum, average, or count while ignoring specified types of data such as hidden rows or errors.

Step-by-Step Breakdown

  1. function_num: Specifies the type of calculation (e.g., 1 for AVERAGE, 9 for SUM).
  2. options: Determines which values to ignore (e.g., 1 to ignore hidden rows, 6 to ignore errors).
  3. array: The range of cells to perform the calculation on.
  4. k: Optional parameter for functions like LARGE or SMALL to specify which value to return.

Edge Cases & Warnings

  • The formula may return unexpected results if the range contains mixed data types.
  • If all values in the range are hidden or errors, the formula may return an error.
  • Using an incorrect function_num may lead to incorrect calculations.

Examples

Prompt

"AGGREGATE(9, 5, A1:A10)"

Excel 
Sum of A1:A10 ignoring hidden rows and errors.
Prompt

"AGGREGATE(1, 6, B1:B10)"

Excel 
Average of B1:B10 ignoring errors.

Frequently Asked Questions

What does the function_num parameter do?

It specifies the type of operation to perform, like SUM or AVERAGE.

Can I use AGGREGATE with non-numeric data?

Yes, but it will only work correctly if the data can be interpreted as numbers.

How do I ignore both hidden rows and errors?

Use a combination of options in the AGGREGATE function to ignore both types.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples