Excel SUBTOTAL Function

Excel Subtotals

This page describes the Excel Subtotal function. However, Excel also has a Subtotal command that inserts subtotals into a data table.

For details, see the Excel Subtotal Command page.

Function Description

The Excel Subtotal function performs a specified calculation (e.g. the sum, product, average, etc.) for a supplied set of values.

The syntax of the function is:

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

Where the arguments are as follows:

function_num -

An integer that denotes the calculation type (e.g. sum, average, etc.).

(See the table below for possible values for function_num).
ref1, [ref2], ... -

One or more references to cells containing the values that the calculation is to be performed on.

(Blank cells and cells containing non-numeric values are ignored in the calculation).


  Table 1: Possible values for function_num
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

The possible values for the function_num argument are shown in Table 1 (see rightabove).

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


Subtotal Function Examples

The following examples all use a simple company spreadsheet, that shows monthly sales figures, between January and March, for 3 teams.


Example 1 - No Values Hidden

In cells C12 and C13 of the spreadsheet below, the Subtotal function is used to calculate the sum and the average monthly sales per team. As all of the cells in the range of sales figures are visible, the calculations include all values in the sales column:

 Formulas:
Example 1 Showing Formulas for the Excel Subtotal Function
 Results:
Example 1 Showing Results for the Excel Subtotal Function

Note that, in the example above, as all cells are visible, we could have set the function_num argument to be either 9 or 109 for the sum, and to be either 1 or 101 for the average calculation. Both options would give the same two results.


Example 2 - Values Hidden Using Excel Autofilter

In this example, the teams column of the spreadsheet has been filtered, using the Excel Autofilter, to show Team 1 only. In this case, the Subtotal function's calculations will be for the visible cells only (i.e. for Team 1 only):

 Formulas:
Example 2 Showing Formulas for the Excel Subtotal Function
 Results:
Example 2 Showing Results for the Excel Subtotal Function

Note that, in the example above, because the rows for Teams 2 & 3 have been filtered out, using the Excel Autofilter, we could have used a function_num argument of either 9 or 109 for the sum and a function_num argument of either 1 or 101 for the average. Both options would perform the calculation on the visible cells only.


Example 3 - Values Hidden Using Row Formatting

In the spreadsheet below, rows 3, 4, 6, 7, 9, and 10 have been hidden using row formatting. I.e. by highlighting these rows, right clicking with the mouse and selecting Hide.

Once again, the Subtotal function has been used in cells C12 and C13 of the spreadsheet, to calculate the sum and average of the monthly sales across the visible teams.

Because the non-visible rows have been hidden using the row formatting option, if you want to only include the visible cells in the calculations, it is necessary to set the function_num argument to the value 109 for the sum calculation, and to the value 101 for the average calculation.

 Formulas:
Example 3 Showing Formulas for the Excel Subtotal Function
 Results:
Example 3 Showing Results for the Excel Subtotal Function

For further details and examples of the Excel Subtotal Function, see the Microsoft Office website.


Subtotal Function Error

If you get an error from the Excel Subtotal function this is likely to be one of the following:

Common Error
#VALUE! - Occurs if the supplied function_num argument is not one of the permitted values (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).