SUBTOTAL Function
Calculates a total, average, count and more — ignoring other subtotals.
Category
Math & Trig
Available in
All versions, Microsoft 365
Returns
A number
Overview
SUBTOTAL runs one of 11 calculations (SUM, AVERAGE, COUNT, MAX…) chosen by a number. Its superpower: it skips any other SUBTOTAL inside the range, so section subtotals and a grand total can live in the same column.
Numbers 1–11 include rows you've hidden by hand; 101–111 leave them out. Rows hidden by a filter are always left out — which is why Table total rows use SUBTOTAL(109, …).
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| function_num | Yes | 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV, 8 STDEVP, 9 SUM, 10 VAR, 11 VARP. Add 100 to skip manually hidden rows. |
| ref1 | Yes | The range to calculate. |
| ref2, … | Optional | More ranges. |
Examples
Example 1 — Section totals and a grand total
Each region has a subtotal; the grand total ignores them automatically.
| A | B | |
|---|---|---|
| 1 | Rep | Sales |
| 2 | Ana (West) | 620 |
| 3 | Cara (West) | 500 |
| 4 | West subtotal | 1120 |
| 5 | Ben (East) | 480 |
| 6 | East subtotal | 480 |
| 7 | Grand total | 1600 |
A plain SUM of B2:B6 would double-count and give 3,200.
Example 2 — Totals that follow a filter
Filter the list by region and the total updates to show only the visible rows.
Common mistakes
- Errors stop it. A #N/A anywhere in the range breaks SUBTOTAL. AGGREGATE can skip errors.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.