What it does
The first argument is a number that says which calculation to run: 1 average, 2 count numbers, 3 count non-empty cells, 4 maximum, 5 minimum, 9 sum. The same numbers plus 100 (101, 102, 109…) run the same calculations.
The difference lies in hidden rows. Rows hidden by AutoFilter are left out either way. Rows you hide by hand (right-click the row header and choose Hide) are included with 9 and left out with 109. If you only filter, 9 is enough; if you also hide rows manually, use 109.
The other key feature of SUBTOTAL is that it skips other SUBTOTAL formulas inside its range. Put a subtotal under each group and a grand total over the whole column at the bottom, and the subtotals are not counted twice; with SUM the grand total would be inflated. If errors must be skipped too, use AGGREGATE.
Syntax
=SUBTOTAL(function_num, ref1, ...)=ALTTOPLAM(işlev_sayısı; başv1; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
function_num | Required | The calculation: 1 average, 2 count numbers, 3 count non-empty, 4 max, 5 min, 6 product, 7-8 standard deviation, 9 sum, 10-11 variance. 101-111 run the same calculations and also skip manually hidden rows. |
ref1 | Required | The first range to subtotal; more ranges can follow. SUBTOTAL formulas inside the range are ignored. |
Examples
1Different function numbers on one range
The formulas in column F summarise the same amount range with different function numbers. There are no hidden rows in this example, so 9 and 109 give the same result.
=SUBTOTAL(9,C2:C6)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Region | Amount | Calculation | Result | |
| 2 | 2-Oct-2025 | West | 12,500.00 | Sum (9) | 38,550.50 | |
| 3 | 6-Oct-2025 | North | 8,300.00 | Sum (109) | 38,550.50 | |
| 4 | 14-Oct-2025 | West | 4,750.50 | Average (1) | 7,710.10 | |
| 5 | 21-Oct-2025 | South | 9,900.00 | Max (4) | 12,500.00 | |
| 6 | 30-Oct-2025 | West | 3,100.00 | Row count (3) | 5 |
- F2
=SUBTOTAL(9,C2:C6) - F3
=SUBTOTAL(109,C2:C6) - F4
=SUBTOTAL(1,C2:C6) - F5
=SUBTOTAL(4,C2:C6) - F6
=SUBTOTAL(3,A2:A6)
The sum is 38,550.50, the average 7,710.10 and the largest amount 12,500.00. If you filtered the list to show only the West, every result would be calculated from the three visible rows.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Put subtotals under each group
The list is sorted by product group, and each group has a subtotal row underneath. Each subtotal covers only the rows of its own group.
=SUBTOTAL(9,C2:C3)| A | B | C | |
|---|---|---|---|
| 1 | Group | Product | Qty |
| 2 | Cable | Copper cable | 1,200 |
| 3 | Cable | Armoured cable | 800 |
| 4 | Cable Total | 2,000 | |
| 5 | Socket | Grounded socket | 250 |
| 6 | Socket | Child-safe socket | 140 |
| 7 | Socket | Outdoor socket | 60 |
| 8 | Socket Total | 450 |
- C4
=SUBTOTAL(9,C2:C3) - C8
=SUBTOTAL(9,C5:C7)
The cable group totals 2,000 and the socket group 450. Data > Subtotal writes the same formulas for you.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: function_num is not between 1-11 or 101-111.
Fix: Use a valid number, such as 9 or 109 for a sum.
- Hidden rows still added
Cause: The rows were hidden by hand rather than filtered, and function_num is 9.
Fix: Use 109 instead of 9.
- Hidden columns added
Cause: SUBTOTAL works on rows; in a horizontal range it does not skip hidden columns with any function number.
Fix: Arrange the data in rows instead of columns, or keep hidden columns out of the range.
Good to know
- In a filtered table, the AutoSum button writes
SUBTOTAL(9,…)instead of SUM. - Data > Subtotal adds subtotal rows with this function to each group of a sorted list.
- To show how many rows remain visible after filtering, use
=SUBTOTAL(3,A2:A500).
In Nexell
Get Nexell to do it from a sentence
Add a total row under the Amount column that adds only the visible rows when a filter is applied
Convert to tableBring your table into the tool and type this. Nexell builds the total row with real Excel formulas and shows it in the preview first; in the downloaded file, the total recalculates for the visible rows whenever you change the filter.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between 9 and 109 in SUBTOTAL?
Both return a sum and skip rows hidden by a filter. 109 also skips rows hidden by hand; 9 includes them. With no hidden rows, both give the same result.
How do I sum only filtered (visible) rows?
Use =SUBTOTAL(9,D2:D500); rows hidden by AutoFilter are left out. SUM adds the hidden rows as well.