Skip to content
Excel

SUBTOTAL function in Excel

Returns a subtotal in a list or database

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

SUBTOTAL syntax and parameters

Three arguments, two required.

  • function_numnumberRequired

    The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.

  • ref1rangeRequired

    The first named range or reference for which you want the subtotal.

  • ref2rangeRepeating

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 Google Sheets

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 Excel gets this done.