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.

Basic Description

The Excel Subtotal function performs a specified calculation (eg. 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-A number that denotes the calculation type (eg. sum, product, average, etc.)
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).


function_num
(include hidden values)
function_num
(ignore hidden values)
Function
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

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

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

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

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 (ie. 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

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, in order 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

Further information on the Excel Subtotal Function is provided on the Microsoft Office website.


Subtotal Function Error

If you get an error from the Excel Subtotal function this is likely to be the #VALUE! error:

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 (eg. finding the average, standard deviation or variance for a range of cells that do not contain any numeric values).