Skip to content

SUBTOTAL

Math & trigExcel 2007

Aggregate a range while ignoring other SUBTOTALs and (optionally) hidden rows.

Syntax

SUBTOTAL(function_num, ref1, [ref2], …)

Arguments

function_num
1 AVERAGE, 9 SUM, 2/3 COUNT, 4 MAX, 5 MIN… Add 100 (109 = SUM) to also skip manually hidden rows.
ref1 …
The ranges to aggregate.

How it works

The function behind the Data → Subtotal feature and filtered-list totals. A SUBTOTAL never counts another SUBTOTAL, so nested totals don't double up.

Examples

=SUBTOTAL(109, C2:C500)

→ sum of visible rows only

Related functions

AGGREGATESUM

More Math & trig functions

ABSACOSACOSHACOTACOTHAGGREGATEARABICASINASINHATANATAN2ATANH

← All 510 functions

Syntax follows Microsoft’s documentation. Examples are written to be pasted into a cell and checked. Argument separators may be ; in your locale. The AI explainer runs on a free community model — verify anything important.