Skip to content
Google Sheets

SUBTOTAL function in Google Sheets

Returns a subtotal for a vertical range of cells using a specified aggregation function.

=SUBTOTAL(function_code, range1, [range2, ...])

SUBTOTAL syntax and parameters

Three arguments, two required.

  • function_codenumberRequired

    The function to use in subtotal aggregation. 1 is AVERAGE 2 is COUNT 3 is COUNTA 4 is MAX 5 is MIN 6 is PRODUCT 7 is STDEV 8 is STDEVP 9 is SUM 10 is VAR 11 is VARP Hidden values can be skipped for any of these codes by prepending 10 (to the single-digit codes) or 1 (to the 2-digit codes). e.g. 102 for COUNT while skipping hidden cells, and 110 for VAR while doing so.

  • range1rangeRequired

    The first range over which to calculate a subtotal.

  • range2rangeRepeating

    Additional ranges over which to calculate subtotals.

SUBTOTAL examples

Formulas you'll actually reuse.

  1. Sum of the VISIBLE rows only — respects filters and hidden rows (109 = SUM):

    =SUBTOTAL(109, C2:C200)

  2. Count of visible entries (103 = COUNTA) — a live “rows shown” indicator:

    =SUBTOTAL(103, A2:A200)

SUBTOTAL in Excel

Same name — your formula ports as-is.

Try SUBTOTAL in the playground

Edit the example — nothing to install.

Preloaded with the SUBTOTAL formula from Example 1 — change anything and watch it respond.

Loading the editor…

Related functions

More ways Google Sheets gets this done.