# SUBTOTAL function in Excel

> SUBTOTAL calculates a sum, average, count or other total for a range while skipping any other SUBTOTAL formulas inside it, so section subtotals and a grand total never double count.

Source: https://spreadsheetpractice.com/functions/subtotal · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** SUBTOTAL calculates a sum, average, count or other total for a range while skipping any other SUBTOTAL formulas inside it, so section subtotals and a grand total never double count.
- **Syntax:** `=SUBTOTAL(function_num, ref1, [ref2], ...)`
- **Most common use:** `=SUBTOTAL(9,B2:B3)` returns $205 (Paper and toner)
- **Available in:** Every current version
- **Category:** [Math functions](https://spreadsheetpractice.com/functions/category/math)
- **Returns:** The result of the chosen calculation over the visible cells, without other subtotals.
- **Google Sheets:** Works the same in Google Sheets.

## What it does

SUBTOTAL calculates a sum, average, count or other total for a range while skipping any other SUBTOTAL formulas inside it, so section subtotals and a grand total never double count. It always leaves out rows hidden by a filter, and with function numbers 101–111 it also leaves out rows you hid by hand, which makes it the total to use under a filtered list.

## Syntax

`=SUBTOTAL(function_num, ref1, [ref2], ...)`

| Argument | Required? | What it is |
|---|---|---|
| `function_num` | Required | Which calculation: 1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV, 8 STDEV.P, 9 SUM, 10 VAR, 11 VAR.P. Add 100 to skip hidden rows. |
| `ref1` | Required | The first range to total. |
| `ref2` | Optional | Optional: more ranges. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| B4 | `=SUBTOTAL(9,B2:B3)` | $205 | Paper and toner |
| B7 | `=SUBTOTAL(9,B5:B6)` | $950 | Chairs and desks |
| B8 | `=SUBTOTAL(9,B2:B7)` | $1,155 | Every item once: the two subtotals inside the range are skipped |

### The sample sheet

Every formula above runs on this data:

|  | A | B |
|---|---|---|
| 1 | Item | Amount |
| 2 | Paper | $120 |
| 3 | Toner | $85 |
| 4 | Supplies subtotal | $205 |
| 5 | Chairs | $340 |
| 6 | Desks | $610 |
| 7 | Furniture subtotal | $950 |
| 8 | Grand total | $1,155 |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| The grand total is double | SUM over a range that already holds subtotals counts them again. Use SUBTOTAL for every total and the grand total skips the others. |
| Hidden rows still count | Numbers 1–11 include rows hidden by hand. Use 101–111, such as SUBTOTAL(109,B2:B20), to skip them. Rows hidden by a filter are always skipped. |
| #VALUE! | The function number isn't 1–11 or 101–111, or the argument is an array rather than a range. |

## Tips

- Add a total row under a filtered list with =SUBTOTAL(109,B2:B500): it changes as you filter.

## Related functions

- [SUM](https://spreadsheetpractice.com/functions/sum): SUM adds numbers.
- [AGGREGATE](https://spreadsheetpractice.com/functions/aggregate): AGGREGATE runs one of 19 calculations, from SUM and AVERAGE to LARGE and PERCENTILE, and can skip error values, hidden rows and other subtotals while it does.
- [SUMIF](https://spreadsheetpractice.com/functions/sumif): SUMIF adds up values for the rows that meet one condition: total travel expenses, revenue for one product, every invoice over $500.

## Questions

### What does 9 mean in SUBTOTAL(9, …)?

It picks SUM. 1 is AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX and 5 MIN; 101–111 are the same but also skip rows hidden by hand.

### How do I sum only visible rows after filtering?

=SUBTOTAL(9,B2:B100). Rows hidden by a filter are always left out; use 109 to leave out rows hidden by hand too.

### How is SUBTOTAL different from AGGREGATE?

AGGREGATE has more calculations, such as MEDIAN, LARGE and percentiles, and can also skip error values.

## Practice

A graded SUBTOTAL exercise ("Totals without double counting") is on [the SUBTOTAL page](https://spreadsheetpractice.com/functions/subtotal). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.