June 18, 2021

How to use the Excel AGGREGATE function

AGGREGATE Function in excel returns the aggregate of a given data table or data lists, this function also has the first argument as function number and further arguments are for a range of the data sets, the function number should be remembered to know which function to use.

Syntax: =AGGREGATE(function_num, options, ref1, [ref2], …)

The AGGREGATE function syntax has the following arguments:

  • function_num  Required. A number 1 through 13 to specify the function, as shown in the Function_Num Table below.
  • options  Required. A number that determines which values to ignore in the evaluation range for the function, as shown in the Options Table below.
  • ref1  Required. The first reference or numeric argument for which you want the aggregate value.
  • ref2  Optional. References or numeric arguments 2 to 253 for which you want the aggregate value.

Example: Let’s look at some Excel AGGREGATE function examples and explore how to use the AGGREGATE function as a worksheet function in Microsoft Excel:

Excel AGGREGATE Function - How to use the Excel AGGREGATE function

Syntax:  =AGGREGATE(2,1,E1:E11)


Excel AGGREGATE Function 1 - How to use the Excel AGGREGATE function

Based on the Excel spreadsheet above, the following AGGREGATE examples would return:

Syntax: =AGGREGATE(2,1,E1:E11)
Result: 253

Syntax: =AGGREGATE(1,5,E1:E11)
Result: #N/A

Syntax: =AGGREGATE(3,5,E1:E11)
Result: 11

Note: The AGGREGATE function returns the result of an aggregate calculation like AVERAGE, COUNT, MAX, MIN, etc. A total of 19 operations are available, and the operation to perform is specified as a number, which appears as the first argument in the function. The second argument, options, controls how AGGREGATE handles errors and values in hidden rows. See tables below for all available options.

READ:  How to use the Excel ROUNDUP function

Leave a Reply

Your email address will not be published. Required fields are marked *