# Excel AGGREGATE Function

The Excel AGGREGATE function returns a aggregate calculation like AVERAGE, COUNT, MAX, etc., optionally ignoring hidden rows and errors. A total of 19 operations are available, specified by function number in the first argument (see table for options).

**function_num**- Operation to perform (1-19).**options**- Values to ignore (0-7).**ref1**- First argument.**ref2**- Second argument (k).

In the example shown, the formula in D5 is:

=AGGREGATE(4,6,values)

where "values" is the named range B5:B14. The function number here is 4, which runs the MAX function.

The formulas in D8:D10 are as follows:

The function number here is 14, which runs the LARGE function. Because the LARGE function requires a k argument, it appears as the last argument in the three formulas above.

### Function numbers

The table below lists the function numbers available to the AGGREGATE function, along with the name of the associated function. The third column, Ref2, indicates the second argument value expected when required.

Function | Function | Ref2 |
---|---|---|

1 | AVERAGE | |

2 | COUNT | |

3 | COUNTA | |

4 | MAX | |

5 | MIN | |

6 | PRODUCT | |

7 | STDEV.S | |

8 | STDEV.P | |

9 | SUM | |

10 | VAR.S | |

11 | VAR.P | |

12 | MEDIAN | |

13 | MODE.SNGL | |

14 | LARGE | k |

15 | SMALL | k |

16 | PERCENTILE.INC | k |

17 | QUARTILE.INC | quart |

18 | PERCENTILE.EXC | k |

19 | QUARTILE.EXC | quart |

### Behavior options

The AGGREGATE function has many options for ignoring errors, hidden rows, and other functions. Possible values are 0-7, as shown in the table below.

Option | Behavior |
---|---|

0 | Ignore SUBTOTAL and AGGREGATE functions |

1 | Ignore hidden rows, SUBTOTAL and AGGREGATE functions |

2 | Ignore error values, SUBTOTAL and AGGREGATE functions |

3 | Ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions |

4 | Ignore nothing |

5 | Ignore hidden rows |

6 | Ignore error values |

7 | Ignore hidden rows and error values |

### Notes

- AGGREGATE returns a #VALUE! error if a second function argument is required, but not provided.
- 3D references do not work with AGGREGATE.
- AGGREGATE function is designed vertical ranges, not horizontal ranges.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.