Practice free

SUBTOTAL function

SUBTOTAL returns a chosen statistic, such as a sum or count, over visible data in a filtered list. It skips other SUBTOTAL results in the range, which prevents double counting.

Example

Read it as a sentence: add up the values in A1:A5 using code 9, which means SUM, which gives 15.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

function_num (required)
1-11 include manually hidden rows; 101-111 skip them. For example 9/109 is SUM, 1/101 AVERAGE, 2/102 COUNT.
ref1 (required)
The range to calculate.
[ref2], ... (optional)
More ranges.

Returns: The chosen statistic over visible data, ignoring other SUBTOTALs in the range.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=SUBTOTAL(9,Data!A1:A5) 15 9 sum
=SUBTOTAL(109,Data!A1:A5) 15 109 sum
=SUBTOTAL(1,Data!A1:A5) 3 1 average
=SUBTOTAL(2,Data!B1:B8) 3 2 count mixed
=SUBTOTAL(3,Data!B1:B8) 7 3 counta mixed
=SUBTOTAL(4,Data!A1:A5) 5 4 max
=SUBTOTAL(0,Data!A1:A5) #VALUE! Function 0
=SUBTOTAL(12,Data!A1:A5) #VALUE! Function 12

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Filtered-out rows are always excluded; only manually hidden rows differ between 9 and 109.
  • It needs references, not array constants.
  • Excel refuses the formula outright if you type an array constant such as {1,2,3} where a range is expected; point it at cells instead (measured in Excel 16.0.20527).

Where it works

Excel
SUBTOTAL is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Math and trig functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .