AGGREGATE function
Returns an aggregate in a list or database
Syntax
AGGREGATE(function_num, options, ref1, [ref2])Square brackets indicate optional arguments.
Arguments
| Argument | Required | Meaning |
|---|---|---|
| function_num | Yes | A number 1 to 19 that specifies which function to use.1 |
| options | Yes | A numerical value that determines which values to ignore in the evaluation range for the function.6 |
| ref1 | Yes | The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.A1:A20 |
| ref2 | No · repeats | Numeric arguments 2 to 252 for which you want the aggregate value.B1:B20 |
Try AGGREGATE
Sum a vertical range while ignoring formula errors
=AGGREGATE(9,6,A2:A6)Function number 9 selects SUM and option 6 ignores error values. The numeric entries total 12-2+0+5=15. The prepared A3 formula creates a real division error rather than a text label. Hidden-row and nested-subtotal options have different meanings; this example tests error exclusion only.
Inspect the current formula
Read its written arguments or observe actual operation values in a separate local calculation. This action neither applies a draft nor grades an answer.
Read the formula, one argument at a time
Explore the written function calls and their inputs. The calculation result belongs to the complete formula.
AGGREGATE · top-level call
=AGGREGATE(9,6,A2:A6)Follow the cells behind a result
Read explicit local references and helper formulas using a separate calculation. Your draft stays in this field; the worksheet and practice checks do not change.
The worksheet runs locally. Published checks apply to the supplied example only. Native Excel and Google Sheets compatibility remain unverified.
Practice a complete task
Reference and compatibility
Univer 1.0.4 / Apache-2.0. Calculation checks cover the supplied examples only. The browser worksheet does not implement every Excel feature, external service or connected data type.