The Excel SUBTOTAL function aggregates a range with one of 11 operations (SUM, AVERAGE, COUNT, MAX, …) while ignoring rows hidden by a filter — and, optionally, rows you hide by hand.
Syntax
| Argument | Description | |
|---|---|---|
function_num | Required | A number choosing the operation: 1-11 (e.g. 9 = SUM, 1 = AVERAGE, 2 = COUNT, 4 = MAX) ignore filtered rows; 101-111 also ignore manually hidden rows. |
ref1 | Required | The first range to aggregate. |
ref2, ... | Optional | Up to 253 more ranges. |
How to use it
SUBTOTAL's first argument picks the math. Codes 1–11 ignore rows hidden by a filter; codes 101–111 also ignore rows you hide manually.
The clever part: SUBTOTAL ignores other SUBTOTAL cells in its range, so a grand total made of SUBTOTAL won't double-count nested subtotals. That's why Excel's Data > Subtotal feature uses it.
Common codes: 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 9 SUM. Add 100 (101, 109, …) to also drop manually hidden rows.
Try it: interactive demo
Pick a SUBTOTAL example to see the formula and its result.
Practice workbook
Frequently asked questions
What does the function_num argument mean?
What is the difference between 9 and 109?
Why doesn't SUBTOTAL double-count other subtotals?
Does SUBTOTAL work on filtered data?
=SUBTOTAL(9,...) totals only the visible (matching) rows and updates as you change the filter.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class