SUBTOTAL

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.

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?

Create a blank spreadsheet.
Select the example in the Help topic.

Selecting an example from Help
Press CTRL+C.
In the spreadsheet, select cell A1, and press CTRL+V.
To switch between viewing the formula that returns the result and the result in the cell, select the cell and press F2 and then ENTER, or click Commands and Options on the spreadsheet toolbar, click the Formula tab, and look in the Formula in active cell (active cell) box.

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)