AGGREGATE

What it does

AGGREGATE(function_num, options, ref1, [ref2], ...)

AGGREGATE(function_num, options, ref1, [ref2], ...) applies one of nineteen aggregate functions to its reference arguments, under an explicit policy about what to skip.

It is not one function but a dispatcher over a table. Microsoft's Learn reference documents the selector as a number from 1 to 19:

and the second argument as an ignore policy, also documented on that page:

The two-bit structure is worth naming, because the table above is really a product: bit 0 is "ignore hidden rows", bit 1 is "ignore error values", and everything below 4 additionally ignores nested SUBTOTAL and AGGREGATE calls. Options 0-3 are the nesting-aware quadrant; 4-7 are the same four policies without the nesting rule.

What AGGREGATE is for is the error-skipping column. Every other route to "average this column but ignore the #N/As" is an array formula or an IFERROR wrapper over each cell; AGGREGATE makes it argument 2. That is the reason the function exists, and it is why the coercion chapter names AGGREGATE as the declared exception to the rule that coercion never silently discards a worksheet error — see Coercion and lifting. The skipping is a per-family declaration, visible here, not an implicit engine behaviour.

Arguments

The argument position that catches everyone

For function_num 14 through 19 — LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC — the second reference argument is not more data. It is the k argument of the selected function. So AGGREGATE(14, 6, A1:A100, 3) is "the third largest, ignoring errors", and the trailing 3 is a rank, not a range. For function_num 1 through 13 the same slot is more data. One signature, two meanings, keyed on the first argument's value.

Microsoft's Learn reference documents the consequences of getting this wrong, and they are worth quoting in effect: if a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error.

Reference types

The Learn page's constraint table is unusually specific, and it is where AGGREGATE departs sharply from ordinary aggregates:

• For function_num 1-13, the reference arguments accept cell references, unions, intersections, defined names and structured references, and explicitly do not accept actual data or arrays.
• For function_num 14-17, arrays and literal data are accepted in the first two slots, and no references are allowed from the third onward.

So AGGREGATE(9, 6, {1,2,3}) is documented as invalid while AGGREGATE(14, 6, {1,2,3}, 1) is documented as valid. That is a real and unusual asymmetry, and it follows from the first group being reference-consuming aggregations while the second group needs a materialised list to sort.

A gap in that table worth recording: it covers function_num 1-13 and 14-17 and stops. function_num 18 and 19 — PERCENTILE.EXC and QUARTILE.EXC — are in the selector table and absent from the constraint table. Their reference-type rules are documented nowhere on the page.

Try it

Quirks

Result and edge cases

Returns whatever kind the selected aggregate returns — in practice Number, or an Error.

The last two rows deserve emphasis. AGGREGATE reads hidden-row state, which is presentation state rather than cell content, and it is nevertheless classified non-volatile. That combination — depends on workbook state, does not recalculate on every change — is the source of the practical complaint that AGGREGATE results can go stale after rows are hidden or shown, and it is a behaviour worth probing rather than assuming.

"Hidden" means hidden rows. The Learn documentation says rows throughout. Hidden columns are not mentioned, and filtered-out rows versus manually hidden rows is a distinction the documentation does not draw here at all.

Errors

Everything else — an out-of-range function_num, an out-of-range options, a ref1 of the wrong kind for the selected function — is undocumented on the page that was retrieved. Those are on the probe list below.

How to implement

Numerical notes

AGGREGATE has no numerics of its own — it computes nothing — but it fixes two things that are usually left implicit, and both belong on this page.

The skip changes the reduction, not the arithmetic. Skipping error values removes terms from the fold. Skipping hidden rows removes different terms. Neither reweights anything, so AGGREGATE(1, 6, ...) — average, ignoring errors — is the mean of the surviving values, with a denominator that depends on how many errors there were. That is the intended reading, and it is worth stating because the alternative reading (treat errors as zero) would give a different and also plausible answer.

Variance and standard deviation inherit their own hazard. function_num 7, 8, 10 and 11 select STDEV.S, STDEV.P, VAR.S, VAR.P. Whether those are computed by the textbook sum-of-squares-minus-square-of-sum formula or by a numerically stable two-pass or Welford recurrence is a property of those functions, not of AGGREGATE, and it is the dominant accuracy question when the data has a large mean and a small spread. See those pages.

Rust code

2 aggregate_fn "AGGREGATE" (3, 253) [V "function_num", V "options", R "ref1", R "ref2"] |s, a, sv| {
    let function = num(s, a, 0)?.trunc() as i64;
    let options = if a[1].is_missing() { 0 } else { num(s, a, 1)?.trunc() as i64 };
    if !(0..=7).contains(&options) || !(1..=19).contains(&function) {
        return fail(WorksheetErrorCode::Value);
    }
    let rule = Rule {
        nested: options <= 3,
        hidden: matches!(options, 1 | 3 | 5 | 7),
        filtered: matches!(options, 1 | 3 | 5 | 7),
        errors: matches!(options, 2 | 3 | 6 | 7),
    };
    if function <= 13 {
        // The reference form reads references only: a value there (a number, an array) is
        // #VALUE! (Excel 16.0.20430, 2026-10-25, dc-6au: `=AGGREGATE(1,1,{1,2})`,
        // `=AGGREGATE(9,0,1)` and `=AGGREGATE(1,1,A1:A3*1)` are #VALUE!). An error value
        // there is not measured and stays refused.
        if a[2..].iter().any(|argument| {
            !matches!(argument.core_ref(), CoreRef::Reference(_) | CoreRef::Error(_))
                && !argument.is_missing()
        }) {
            return fail(WorksheetErrorCode::Value);
        }
        let values = held_values(s, a, 2, sv, rule)?;
        return compute(s, function, &values);
    }
    // The array form: AGGREGATE(function_num, options, array, k).
    if a.len() != 4 {
        return fail(WorksheetErrorCode::Value);
    }
    let k = num(s, a, 3)?;
    let values = match a[2].core_ref() {
        CoreRef::Reference(_) => held_values(s, &a[..3], 2, sv, rule)?,
        CoreRef::Array(array) => array
            .iter_row_major()
            .filter(|v| !(rule.errors && matches!(v.core_ref(), CoreRef::Error(_))))
            .cloned()
            .collect(),
        _ => vec![a[2].clone()],
    };
    let mut numbers = numbers_of(&values)?;
    let result = match function {
        14 | 15 => {
            let k = k.trunc();
            if k < 1.0 || k > numbers.len() as f64 {
                return fail(WorksheetErrorCode::Num);
            }
            numbers.sort_by(f64::total_cmp);
            if function == 14 {
                numbers.reverse();
            }
            Ok(numbers[k as usize - 1])
        }
        16 => percentile_inc_kernel(&mut numbers, k),
        17 => {
            let q = k.trunc();
            if !(0.0..=4.0).contains(&q) { Err(WorksheetErrorCode::Num) }
            else { percentile_inc_kernel(&mut numbers, q / 4.0) }
        }
        18 => percentile_exc_kernel(&mut numbers, k),
        _ => {
            let q = k.trunc();
            if !(1.0..=3.0).contains(&q) { Err(WorksheetErrorCode::Num) }
            else { percentile_exc_kernel(&mut numbers, q / 4.0) }
        }
    };
    super::kit::out(s, result)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• The nineteen selected functions — each has its own Handbook page, and each is the authority on what its own aggregation means. AGGREGATE changes which values reach those functions, not what they compute.
• IFERROR, ISERROR, IFNA — the pre-AGGREGATE idiom for error skipping, one cell at a time.
• FILTER and the dynamic-array functions — the modern alternative, which does the selecting explicitly and then aggregates normally. Where AGGREGATE's policy is a magic number, FILTER's is a visible predicate; that is usually the better formula, and it does not know about hidden rows.
• COUNTIFS / SUMIFS / AVERAGEIFS — criteria-based selection rather than error-and-visibility-based selection. Different axis, frequently confused.
• Confused with: SUBTOTAL, constantly, and by readers who assume AGGREGATE's option 0 and SUBTOTAL's default behave alike.

Named variants

No named variants are recorded yet.