SUBTOTAL

What it does

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

SUBTOTAL(function_num, ref1, [ref2], ...) applies one of eleven aggregate functions to its references, with two structural behaviours layered on top:

SUBTOTAL(f, R) = A_f ( { v in R : v is visible under the active policy,
                         and v is not itself part of a nested SUBTOTAL result } )

where A_f is the aggregate selected by function_num.

The dispatch table, exactly as Microsoft documents it:

SUBTOTAL has no mathematics of its own. Everything numerical about it is inherited from the kernel it selects, and the eleven kernels span three quite different numerical characters: plain reductions (SUM, PRODUCT, MAX, MIN, counting), a ratio (AVERAGE), and the four second-moment functions (STDEV, STDEVP, VAR, VARP) whose stability is a research topic in its own right. That heterogeneity is the point of the Numerical notes below.

The two structural behaviours are what actually make SUBTOTAL different from calling the aggregate directly:

1. Nested subtotals are excluded. "If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting." This is what permits the classic layout — a grand total that spans a column already containing group subtotals — to be written without arithmetic gymnastics. It is a self-referential rule: the function inspects the formulas in its reference range, not merely their values.
2. Invisible rows may be excluded, on two different and independently specified axes. See below.

Arguments

The commonly misunderstood position is function_num, and specifically the belief that the 1xx codes are "the same but better". They are not the same: they differ on manually hidden rows and agree on everything else. Choosing between them is choosing a policy about hidden data, and the right choice depends on why the rows are hidden.

Try it

Quirks

The two number ranges

The distinction between 1-11 and 101-111 is not the same as the distinction between hidden and filtered, and conflating them is the most common SUBTOTAL mistake. Microsoft's two statements, side by side:

• Codes 1-11 include manually hidden rows; codes 101-111 exclude them.
• "The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use."

So there are two ways for a row to be invisible and they are treated differently:

Filtering is unconditional; manual hiding is the axis the code range controls. A reader who hides rows by hand and expects SUBTOTAL(9, ...) to drop them will get a total that silently includes them.

Two further documented constraints belong here:

• SUBTOTAL is designed for vertical ranges. Microsoft states it plainly: it "is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges." Hidden columns have no corresponding rule, which is the practical content of the statement — the visibility machinery is row-shaped.
• 3-D references are rejected. "If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value."

Result and edge cases

Returns Number. The kind and meaning of that number are whatever the selected aggregate produces.

• Empty visible set. What SUBTOTAL(1, ...) returns when every row is filtered out is not documented; AVERAGE over nothing is #DIV/0! and SUM over nothing is zero, so the answer depends on the kernel. The Handbook has not checked.
• function_num out of the documented set — 0, 12, 100, 112, a non-integer — is not documented. #VALUE! is the plausible answer and the Handbook does not assert it.
• Text, logicals and empty cells in the references follow each kernel's own scan policy, which is not uniform across the eleven: COUNT and COUNTA differ from each other by exactly this, and that difference is preserved through the dispatch.

Errors

How to implement

Numerical notes

SUBTOTAL performs no arithmetic of its own; its numerical behaviour is a union of eleven kernels' behaviours plus one property of its own.

The kernels split into three difficulty classes.

2. AVERAGE. A sum followed by a division: one extra rounding, and the sum's error carried through. Nothing structural.
3. STDEV, STDEVP, VAR, VARP — the interesting ones. The textbook computational formula (SUM x^2 - n*mean^2)/(n-1) subtracts two large nearly-equal quantities and can return a negative variance on data with a large mean and small spread. Chan, Golub and LeVeque's classic paper catalogues the alternatives and their error behaviour: the two-pass algorithm (compute the mean, then the sum of squared deviations) is accurate and needs the data twice; Welford's updating recurrence is accurate, needs one pass, and is what most modern libraries use. Which of these a given implementation of STDEV uses is not something the worksheet can see — and SUBTOTAL(7, ...) inherits it wholesale.

The dispatch adds one more consideration: the visible subset changes the conditioning. A variance over a filtered subset can be far worse conditioned than the variance over the whole column, because filtering can leave a set with a large mean and a tiny spread. A SUBTOTAL(10, ...) that looks stable on the unfiltered data can become numerically fragile the moment a filter is applied, and nothing in the interface signals it.

The Handbook makes no claim about which variance algorithm Excel uses in any of these kernels.

Rust code

1 subtotal "SUBTOTAL" (2, 255) [V "function_num", F "ref1", F "ref2"] |s, a, sv| {
    let number = num(s, a, 0)?.trunc() as i64;
    let (function, hidden) = match number {
        1..=11 => (number, false),
        101..=111 => (number - 100, true),
        _ => return fail(WorksheetErrorCode::Value),
    };
    let rule = Rule { nested: true, hidden, filtered: true, errors: false };
    let values = held_values(s, a, 1, sv, rule)?;
    compute(s, function, &values)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• The eleven kernels — AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP. Note that the dispatch table names the compatibility forms (STDEV, STDEVP, VAR, VARP), not the modern STDEV.S / STDEV.P / VAR.S / VAR.P names. SUBTOTAL is a fixed table written before that renaming and it did not move.
• SUMIFS / COUNTIFS — the criteria-based alternative when the filter is a data condition rather than a UI state. They are deterministic in a way SUBTOTAL is not, because their filter lives in the formula.
• SUM — the direct call, which includes everything regardless of visibility and does not skip nested subtotals. Replacing SUBTOTAL(9, ...) with SUM(...) in a table that has group subtotals double-counts.
• Confused with: the Subtotal command on the Data tab, which is a UI feature that inserts SUBTOTAL formulas and grouping outlines. The function and the command share a name and are not the same thing.

Named variants

No named variants are recorded yet.