Skip to content
Excel

AGGREGATE function in Excel

Returns an aggregate in a list or database

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

AGGREGATE syntax and parameters

Four arguments, three required.

  • function_numnumberRequired

    A number 1 to 19 that specifies which function to use.

  • optionsanyRequired
  • ref1anyRequired

    The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.

  • ref2anyRepeating

AGGREGATE examples

Formulas you'll actually reuse.

  1. A SUM that skips error cells (option 6 = ignore errors):

    =AGGREGATE(9, 6, C2:C200)

    Result148200

  2. The second-largest value with errors ignored (14 = LARGE):

    =AGGREGATE(14, 6, C2:C200, 2)

    Result9800

AGGREGATE in Google Sheets

No direct equivalent — here's the way around.

Try AGGREGATE in the playground

Edit the example — nothing to install.

Preloaded with the AGGREGATE formula from Example 1 — change anything and watch it respond.

Loading the editor…

Related functions

More ways Excel gets this done.