1. Home
  2. Functions
  3. Math
  4. SUBTOTAL
Math

SUBTOTAL function

Returns a subtotal (sum, average, count and more) of ranges, skipping other subtotals; codes 101 to 111 also skip hidden rows.

Syntax

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

Arguments

ArgumentRequiredDescription
function_numRequired1 AVERAGE, 2 COUNT, 3 COUNTA, 4 MAX, 5 MIN, 6 PRODUCT, 7 STDEV, 8 STDEVP, 9 SUM, 10 VAR, 11 VARP; add 100 to skip hidden rows
ref1RequiredThe first range
ref2OptionalMore ranges

How to use SUBTOTAL

SUBTOTAL takes 2 required arguments and 1 optional argument, and accepts more values in the same pattern. Type =SUBTOTAL( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.

Examples

Each result below was calculated by the SheetsBat formula engine.

=SUBTOTAL(9, A1:A3)Result 6
Data in the sheet
A
11
22
33
=SUBTOTAL(1, A1:A3)Result 2
Data in the sheet
A
11
22
33
=SUBTOTAL(9, A1:A3)Result 4
Data in the sheet
A
11
2=SUBTOTAL(9,A1)
33

Every formula, right in your browser

SheetsBat calculates 536 functions, with charts, pivot tables and live collaboration. Free to start.

Open a blank sheetBrowse templates