subtotal

Aggregate over visible cells using a function code.

Syntax

v = subtotal(fn, ref1, ref2, ...)

Description

Returns an aggregate (mean, sum, max, etc.) over the cells in ref1, ref2, ..., choosing the operation by integer code fn:

• 1/101 — average
• 2/102 — count (numbers)
• 3/103 — counta
• 4/104 — max
• 5/105 — min
• 6/106 — product
• 7/107 — stdev_s
• 8/108 — stdev_p
• 9/109 — sum
• 10/110 — var_s
• 11/111 — var_p

Codes ≥100 ignore manually hidden rows.

Example

subtotal(9, A1:A20)    % sum, equivalent to sum(A1:A20)

See also

• sum — Sum elements of an array.
• average — Arithmetic mean of numeric arguments (Excel-compatible).
• count — Count numeric values in the arguments.