SUBTOTAL handles 11 aggregation types. AGGREGATE handles 19 — and it ignores errors. It does everything SUBTOTAL does, then adds MEDIAN, LARGE, SMALL, PERCENTILE, and QUARTILE to the same filter-aware framework. When a #DIV/0! in one cell breaks your SUBTOTAL total, AGGREGATE skips it. When you need the median of filtered data — which SUBTOTAL cannot compute — AGGREGATE delivers it with a single formula. This guide covers both syntax forms, all 19 function numbers, all 8 option codes, and six practical examples: error-tolerant SUM and AVERAGE, filter-aware MEDIAN and five-number summary, k-th LARGE and SMALL on filtered data, PERCENTILE and IQR outlier bounds, a trimmed mean that removes extreme outliers before averaging, and visible-row RANK. It also explains the most important option choice: use option 5 for most filtered tables, option 7 when data also contains errors, and options 0–3 in grouped reports to prevent grand totals from double-counting group subtotals.

Filter a table and SUM keeps counting every row — including the hidden ones. SUBTOTAL fixes this. It is the only native Excel function that automatically ignores filtered-out rows, updating the moment a filter is applied or removed. This guide covers all 11 aggregation types across both function number ranges (1–11 and 101–111), with eight practical examples: filter-aware SUM, COUNT, AVERAGE, MAX and MIN; group subtotals with a grand total that avoids double-counting; the difference between function 9 and 109 when rows are hidden manually; using SUBTOTAL(103) as a per-row visibility indicator for filter-aware conditional sums; Excel Table Total Row integration; AGGREGATE for median, LARGE, and error-tolerant totals; a live KPI dashboard with a “Showing N of M deals” label; and why SUMIF fails on filtered data — and how to fix it.