What SUBTOTAL does
SUBTOTAL summarizes a column of data and leaves out rows excluded by a filter. Use it when your total, average, or count needs to follow the rows selected in a filtered list.
The first argument chooses the calculation and tells Excel how to handle rows you hide manually. For example, 9 adds the values and includes manually hidden rows; 109 adds the values but excludes those rows. Both ignore filtered-out rows.
| Calculation | Include manually hidden rows | Ignore manually hidden rows |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Enter the number in the formula, not the calculation name. STDEV and VAR use sample calculations; STDEVP and VARP use population calculations.
Practical examples
Total the amounts before filtering
Enter these amounts in A2:A5, with the heading Amount in A1. Start with all four rows visible and no filter applied. Put the formula in C7, outside the data range.
| Cell | Amount |
|---|---|
| A2 | 120 |
| A3 | 10 |
| A4 | 150 |
| A5 | 23 |
=SUBTOTAL(9,A2:A5)
The result is 303: 120 + 10 + 150 + 23. Function number 9 selects SUM.
Compare manual hiding with filtering
Using the same four amounts, keep the filter cleared and manually hide worksheet row 3, which contains 10. Enter this formula in C8:
=SUBTOTAL(109,A2:A5)
The result is 293: 120 + 150 + 23. The formula with 9 in C7 still returns 303, because it includes manually hidden rows.
Now unhide row 3. Apply a filter to A1:A5 and exclude the value 10, leaving the other three amounts visible. The formulas with 9 and 109 now both return 293. Filtering excludes that row from either calculation.
Average the filtered amounts
Keep the same filter applied: A2 = 120, A4 = 150, and A5 = 23 are visible; A3 = 10 is filtered out. Enter this formula in C9:
=SUBTOTAL(101,A2:A5)
The result is 97.666…, or 97.67 when displayed to two decimal places: (120 + 150 + 23) / 3. Function number 101 selects AVERAGE and also excludes manually hidden rows.
Common mistakes and notes
Manual hiding and filtering are different
Choose 101–111 when manually hidden rows must be excluded. Changing to 1–11 includes manually hidden rows, but it never brings filtered-out rows back into the calculation.
Existing subtotals are skipped
If a referenced range contains another SUBTOTAL formula, SUBTOTAL ignores that nested result to avoid counting the same data twice.
Hiding columns does not have the same effect
SUBTOTAL is designed for vertical ranges. In a horizontal range such as =SUBTOTAL(109,B2:G2), hiding a column does not remove its value from the total.
References cannot span multiple worksheets
A 3-D reference, such as Sheet1:Sheet3!A2:A5, produces #VALUE!. Supply ranges on an individual worksheet instead.