AGGREGATE performs calculations such as:
- AVERAGE
- COUNT
- COUNTA
- MAX
- MIN
- SUM
- LARGE
- SMALL
It also provides options to ignore hidden rows, errors, or nested SUBTOTAL/AGGREGATE results.
Syntax
=AGGREGATE(function_num,options,array)
Example
=AGGREGATE(9,5,C2:C10)
Here:
- 9 = SUM
- 5 = Ignore hidden rows
- C2:C10 = Data range
Useful Function Numbers
|
Number |
Function |
|
1 |
AVERAGE |
|
2 |
COUNT |
|
3 |
COUNTA |
|
4 |
MAX |
|
5 |
MIN |
|
9 |
SUM |
|
14 |
LARGE |
|
15 |
SMALL |
Real-World Use
AGGREGATE is useful when working with large reports where some rows may be hidden or contain errors.