Returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command (Data menu). 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 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.
Function_num (includes hidden values) 
Function_num (ignores hidden values) 
Function 
1 
101 
AVERAGE 
2 
102 
COUNT 
3 
103 
COUNTA 
4 
104 
MAX 
5 
105 
MIN 
6 
106 
PRODUCT 
7 
107 
STDEV 
8 
108 
STDEVP 
9 
109 
SUM 
10 
110 
VAR 
11 
111 
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.
 For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide command under the Row submenu of the Format menu). Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide command under the Row submenu of the Format menu). Use these constants when you want to subtotal only nonhidden numbers in a list.
 The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
 The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.
 If any of the references are 3D references, SUBTOTAL returns the #VALUE! error value.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
 Create a blank workbook or worksheet.
 Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In the worksheet, select cell A1, and press CTRL+V.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A 
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) 
