SUBTOTAL and AGGREGATE in Excel

SUBTOTAL and AGGREGATE compute totals, averages, and counts while intelligently ignoring other subtotals and, optionally, hidden or filtered rows. AGGREGATE goes further by skipping errors and hidden values that would otherwise break a plain SUM. In financial models they produce clean section totals over filtered data and survive error cells, which makes them more robust than SUM or AVERAGE for summary lines.

Syntax and the function number

SUBTOTAL is =SUBTOTAL(function_num, ref1, [ref2], ...). The first argument is a code that selects the operation: 9 for SUM, 1 for AVERAGE, 2 for COUNT, 3 for COUNTA, 4 for MAX, and 5 for MIN. Codes 1 to 11 respect filters but include manually hidden rows, while codes 101 to 111 also ignore manually hidden rows.

AGGREGATE is =AGGREGATE(function_num, options, ref1, [ref2], ...). Its function numbers run 1 to 19, covering the SUBTOTAL set plus extras like 14 for LARGE and 15 for SMALL. The second options argument controls what to ignore, from 0 for nested subtotals up to 7 for hidden rows, errors, and nested subtotals together. Both functions automatically skip any nested SUBTOTAL or AGGREGATE inside their range, which prevents double counting.

Worked example: filtered section total

A list of amounts sits in D2:D20, and you apply a filter so only some rows are visible. A plain =SUM(D2:D20) still adds the hidden rows, but SUBTOTAL with code 9 adds only the visible, filtered rows.

1. Enter =SUBTOTAL(9, D2:D20) in the total cell.
2. Code 9 selects SUM and respects the active filter, so only displayed rows are added.
3. Filter the list and the total updates automatically to the visible subset.
4. To also exclude rows you hid manually rather than by filter, use code 109: =SUBTOTAL(109, D2:D20).

Codes 101 to 111 ignore manually hidden rows; codes 1 to 11 do not.

Why analysts use them in models

These functions solve two recurring problems: stacking section totals without double counting, and summarizing data that may contain errors or be filtered down to a subset.

AGGREGATE in particular earns its place because it can ignore error cells, letting a grand total survive a #DIV/0! somewhere in the column instead of propagating it.

• Stack subtotals safely: a grand-total =SUBTOTAL(9, D2:D50) skips any section SUBTOTAL inside the range, so totals never double count.
• Total visible data only: pair SUBTOTAL(9, ...) with filters for clean section sums that track the view.
• Ignore errors: =AGGREGATE(9, 6, D2:D20) sums the range while skipping any error cells, where option 6 means ignore errors.
• Reach further: AGGREGATE codes 14 and 15 return the kth largest or smallest value, useful for top-N analysis on filtered ranges.

Common pitfalls in models

The function-number codes are easy to confuse. Using 9 when you meant 109, or 1 when you meant 101, changes whether manually hidden rows are counted, and the difference is silent. Decide up front whether hidden rows should be in scope and use the matching code consistently.

The second trap is buried literals. The function number and the AGGREGATE option code are constants typed inside the formula, and a wrong code looks like every other valid formula to a reviewer. SUBTOTAL also only ignores rows hidden by row hiding or filtering, not columns, so hiding columns does not change the result. Confirm the codes deliberately rather than copying them from another cell.