# 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
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

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:

• 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 or vertical ranges of data. 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.

## 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: Results: 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: Results: 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: Results: 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).