ExcelFunctions.net

Search Site:

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

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

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

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

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.

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

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

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

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