- Advanced Excel Functions Tutorial
- Advanced Excel Functions - Home
- Compatibility Functions
- Advanced Excel Functions - Cube
- Database Functions
- Date & Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Advanced Excel Functions - Logical
- Lookup & Reference Functions
- Math & Trignometric Functions
- Statistical Functions
- Useful Resources
- Quick Guide
- Useful Resources
- Discussion
Math and Trignometric - SUBTOTAL Function
Description
The SUBTOTAL function returns a subtotal in a list or database. You can create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in Excel. Once the subtotal list is created, you can modify it by editing the SUBTOTAL Function.
Syntax
SUBTOTAL (function_num, ref1, [ref2]...)
Arguments
Argument | Description | Required/Optional |
---|---|---|
Function_num | The number 1-11 or 101-111 that specifies the function to use for the subtotal.
Filtered-out cells are always excluded. Look at the Function_num Table below to know the Functions. |
Required |
Ref1 | The first named range or reference for which you want the subtotal. | Required |
Ref2... | Named ranges or references 2 to 254 for which you want the subtotal. | Optional |
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 |
Notes
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 Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab in Excel. 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 Rows command. 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.
If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value
If the specified function_num is not one of the permitted values (an integer between 1 & 11 or between 101 & 111), SUBTOTAL returns the #VALUE! error value.
If the required calculation involves a division by zero (e.g. finding the average, standard deviation or variance for a range of cells that do not contain any numeric values), SUBTOTAL returns the #DIV/0!error value.
Applicability
Excel 2007, Excel 2010, Excel 2013, Excel 2016