SUBTOTAL

Microsoft Office Spreadsheet Functions

Show All

SUBTOTAL

See Also

Returns a subtotal in a list or database. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

Syntax

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

Function_num   is the number from 1 to 11 that specifies which function to use in calculating subtotals within a list.

Function_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Ref1, ref2, ... are 1 to 29 ranges or references for which you want the subtotal.

Remarks

  • If there are other subtotals within ref1, ref2, … (or nested subtotals), these nested subtotals are ignored to avoid double counting.
  • SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered.
  • If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.

Example

The example may be easier to understand if you copy it to a blank spreadsheet.

How?

Data
120
10
150
23
Formula Description (Result)
=SUBTOTAL(9,A2:A5) Subtotal of the column above using the SUM function (303)
=SUBTOTAL(1,A2:A5) Subtotal of the column above using the AVERAGE function (75.75)