The SUBTOTAL function performs a specified calculation, e.g. sum, product, average, etc., for a supplied set of values, such as a list or database.

Syntax

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

Arguments

Argument Description
function_num A number that denotes the calculation type, eg. sum, product, average, etc. Numbers 1-11 includes manually-hidden rows, while 101-111 excludes them
ref1,
[ref2], …
One or more references to cells containing the values that the calculation is to be performed on
function_num
(include hidden values)
function_num
(ignore 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

Note: The possible values for the function_num argument are shown in the table on the right.

For each arithmetic operation, there is an option to ignore or to include hidden values in the calculation.

  • The ignore/include hidden values option only applies to rows that have been hidden using the ‘Hide’ command in the row formatting options
  • If rows of data have been filtered out using the Excel Autofilter, these are not included in any SUBTOTAL calculations, regardless of the ignore/include hidden values option
  • The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. Although the function can be used to perform arithmetic operations on vertical or horizontal ranges of data, the ‘ignore hidden values’ options will not ignore data in hidden columns.

Examples

  A B C D E
1 Data        
2 100        
3 20        
4 130        
5 87        
6          
7 Formula Result Notes
8 =SUBTOTAL(8,A2:A5) 40.23912897 The standard deviation of the subtotal of the cells A2:A5, using 8 as the first argument
9 =SUBTOTAL(9,A2:A5) 337 The sum of the subtotal of the cells A2:A5, using 9 as the first argument
10 =SUBTOTAL(1,A2:A5) 84.25 The average of the subtotal of cells A2:A5, using 1 as the first argument

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if the supplied function_num argument is not one of the permitted values, i.e. an integer between 1 & 11 or between 101 & 111
#DIV/0! Occurs if 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