Function AGGREGATE

This is a function that is used to aggregate – group data from a given range, which is done by specifying a parameter that calls a specific function (SUM, AVERAGE, COUNT, MAX, MIN, etc.). We can also perform aggregation using other functions, but this one is specific in that it offers various options that allow you to ignore nested functions, hidden rows, errors …

This is the syntax of the AGGREGATE function:

AGGREGATE(<function>,<options>,<range>,[<k>])

The first argument is a number that indicates the function used to aggregate the data. These are the most commonly used mathematical and statistical functions.

The second argument presents additional options:

  • 0 or omitted – ignoring the nested SUBTOTAL and AGGREGATE functions
  • ignoring hidden rows and nested SUBTOTAL and AGGREGATE functions
  • Ignoring errors and nested SUBTOTAL and AGGREGATE functions
  • ignoring hidden rows, errors and nested SUBTOTAL and AGGREGATE functions
  • ignoring nothing
  • ignoring hidden rows
  • ignoring errors
  • ignoring errors and hidden lines

The third argument is at least one range of data, and the fourth is an additional option that may relate to the application of certain functions.

How is this used in practice? Imagine that you want to add up all the sales values ​​but you have previously filtered the table according to some given criteria. The sum will not be correct. However, if we apply the function:

=AGGREGATE(9,5,$B$2:$B$10)

it will add only the values ​​that are visible …