AGGREGATE

Formulas / AGGREGATE
Calculate an aggregation.
=AGGREGATE(function_num, options, ref1, [ref2], ...)
  • function_num - required argument that determines which function to use
  • options - required argument that determines which values to ignore in the function's range
  • ref1 - required argument for functions that take multiple numeric arguments that need aggregating
  • ref2 - [OPTIONAL] additional arguments for functions that take multiple numeric arguments

Examples

  • =AGGREGATE(4,7,A1:A10)

    returns the maximum value in the range A1:A10. This example finds the largest value in the range A1 to A10.

  • =AGGREGATE(5,7,A1:A10)

    returns the minimum value in the range A1:A10. This example finds the smallest value in the range A1 to A10.

  • =AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)

    returns the largest value on Mondays and ignores errors. This example uses the AGGREGATE function to find the largest value on Mondays, while ignoring any errors that might appear.

  • =AGGREGATE(14,6,values,1)

    returns the first largest number in the values. This example finds the largest value in the values provided.

  • =AGGREGATE(14,6,values,2)

    returns the second largest number in the values. This example finds the second largest value in the values provided.

  • =AGGREGATE(14,6,values,3)

    < returns the third largest number in the values. This example finds the third largest value in the values provided.


Summary

The AGGREGATE function can be used to perform aggregations in a list or database and it can ignore hidden rows and error values. It is meant for vertical ranges, but not for horizontal ranges.

  • The AGGREGATE function is like the SUBTOTAL function, but it has more calculation options and can ignore more specific things.
  • AGGREGATE returns an aggregate calculation, such as AVERAGE, COUNT, MAX, and MIN.
  • The AGGREGATE function can optionally ignore hidden rows, errors, and functions appearing in data.
  • The AGGREGATE function can run 19 different functions, which is specified in the first argument as a number.

Frequently Asked Questions

What is the AGGREGATE Function?
The AGGREGATE Function performs aggregations on a list or database. It takes a numerical options argument which determines which values to ignore in the aggregate calculation.
What can the AGGREGATE function do?
The AGGREGATE function can ignore hidden rows, error values and is made for columns. The function_num argument determines which function AGGREGATE should use.
What will the AGGREGATE function throw an error on?
The AGGREGATE function throws a #VALUE! error if there is an unprovided but required second argument or if given one or more 3-D references.
What is the AGGREGATE Function not made for?
The AGGREGATE Function is not made for rows. It is only suitable for columns.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV