Excel functions / Math

AGGREGATE function

Like SUBTOTAL with more calculations, and it can ignore errors in the range.

Syntax

=AGGREGATE(function_num, options, ref, [k1, [k2], …])

Arguments

When to use it

AGGREGATE is SUBTOTAL with more control. It can add, average, count and rank like SUBTOTAL, and a second argument says what to leave out: hidden rows, error values, or both.

Its most common use is totalling a column that has errors in it. One #N/A makes SUM return #N/A; =AGGREGATE(9, 6, …) adds the rest.

Examples

  1. Add up a column that has an error in it

    ABCD
    1ItemPrice
    2Pen1.210.1
    3Folder#N/A
    4Stapler8.9

    In D2: =AGGREGATE(9, 6, B2:B4) → 10.1

    9 means sum, and 6 means ignore errors. =SUM(B2:B4) would return #N/A.

    Try this example

  2. The average, ignoring errors

    ABCD
    1ItemPrice
    2Pen1.25.05
    3Folder#N/A
    4Stapler8.9

    In D2: =AGGREGATE(1, 6, B2:B4) → 5.05

    Try this example

  3. The largest value, ignoring errors

    ABCD
    1ItemPrice
    2Pen1.28.9
    3Folder#N/A
    4Stapler8.9

    In D2: =AGGREGATE(14, 6, B2:B4, 1) → 8.9

    14 is LARGE, which needs a fourth argument: 1 for the largest, 2 for the second largest.

    Try this example

Common mistakes

Common questions

How do I sum a column and ignore errors in Excel?

Use =AGGREGATE(9, 6, B2:B100). The 9 means sum and the 6 means ignore error values, so cells showing #N/A or #DIV/0! are left out of the total.

AGGREGATE in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try AGGREGATE in the playground

Related functions