Skip to content

Excel function · Math

Excel SUBTOTAL function

SUBTOTAL calculates a subtotal of a range with the method you choose (sum, average, count…), ignoring rows hidden by a filter and other SUBTOTALs in the range. It is the right function for the total row of a filtered list.

In Turkish Excel: ALTTOPLAM

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

English Excel=SUBTOTAL(function_num, ref1, ...)
Turkish Excel=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

ArgumentRequiredDescription
function_numRequiredThe 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.
ref1RequiredThe 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.

F2fx=SUBTOTAL(9,C2:C6)
Different function numbers on one range: example table
ABCDEF
1DateRegionAmountCalculationResult
22-Oct-2025West12,500.00Sum (9)38,550.50
36-Oct-2025North8,300.00Sum (109)38,550.50
414-Oct-2025West4,750.50Average (1)7,710.10
521-Oct-2025South9,900.00Max (4)12,500.00
630-Oct-2025West3,100.00Row 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.

C4fx=SUBTOTAL(9,C2:C3)
Put subtotals under each group: example table
ABC
1GroupProductQty
2CableCopper cable1,200
3CableArmoured cable800
4Cable Total2,000
5SocketGrounded socket250
6SocketChild-safe socket140
7SocketOutdoor socket60
8Socket Total450
  • 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

Type this in Nexell

Add a total row under the Amount column that adds only the visible rows when a filter is applied

Convert to table

Bring 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.

Related functions

All math functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free