Excel - Microsoft Excel Subtotal Function

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: 

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

You will notice that you can choose to ignore or to include hidden values. This applied only to rows that have been hidden using the 'Hide' command in the row or column formatting options. 

Note that the option to include hidden cells does not apply to cells that have been filtered out using the Excel Autofilter. Filtered out cells are not included in any Subtotal calculations. This is illustrated in the examples below. 

Subtotal Function Examples

The following examples use a simple spreadsheet that shows monthly sales figures, between January and March, for 3 teams. In each of the examples, the spreadsheet on the left shows the format of the Subtotal function, and the spreadsheet on the right shows the result. 

Example 1

In this example, 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: 

Note that, in the example above, as all cells are visible, we could have set the function_num argument to either 9 or 109 for the sum - both values would give the same result. Similarly, using either 1 or 101 for the average would give the same results. 

Example 2

In this example, the teams have 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) : 

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 to give the same sum, (ie. the sum for the Team 1 sales only). Similarly, we could have used either 1 or 101 to give the same the average calculation. 

Example 3

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

In the spreadsheet below, the Subtotal function is used to calculate the sum and the average monthly sales across ALL teams. Because we are using the values of 9 and 1 for the function_num arguments, the hidden cells are included in the calculations : 

Note that, in the example above, we could exclude the hidden values from the calculations by using the function_num values of 109 and 101 for the sum and average functions respectively.