ExcelFunctions.net

Search site:

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

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

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.

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:

Formulas:
Results:

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.

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) :

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

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 :

Formulas:
Results:

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.

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