What is the Excel SUBTOTAL Function?
The Excel SUBTOTAL function returns a value from 11 available functions such as SUM, COUNT, AVERAGE, MIN, MAX, etc. (see table below for complete function list) with an option to include or exclude the hidden rows.
Another way to calculate subtotals is using the “Subtotal” command in the “Outline” group on “Data” tab
Use Excel AGGREGATE function for more functions and options. The AGGREGATE function has 19 available function and eight options
SUBTOTAL Syntax
SUBTOTAL(function_num,ref1,[ref2],...)
function_num, required. The number 1-11 or 101-111, specifies which function to use for subtotal calculation (see table below for full list).
ref1, required, the first range or reference to include in the subtotal calculation.
[ref2], optional, the other range or reference to include in the subtotal calculation. Handles up to 254 range/reference.Available function
function_num argument determines the SUBTOTAL function return values. The table below shows the available function_num and the corresponding functions.
Usage note:
- function_num between 1-11, SUBTOTAL includes rows that manually hidden.
- function_num between 101-111, SUBTOTAL ignores rows that manually hidden.
- SUBTOTAL ignores rows that are hidden by a filter. “Filtered out” rows are not included, regardless of function_num number.
- SUBTOTAL ignores other SUBTOTAL in ref argument to avoid double-counting.
- SUBTOTAL designed for vertical ranges. In Horizontal ranges, values in hidden columns are always included, hiding a column does not affect the subtotal, even using the 101-111 function_num.
How to Use SUBTOTAL Function in Excel
For example, there is data as shown below. Numbers 1 to 10 are in row 2 to row 11. Cell C13, C14 and C15 contain a formula.
What are the results of the SUM, SUBTOTAL 9 and SUBTOTAL 109 function
- If no hidden rows
- If there are hidden rows with “Hide Rows” menu (/, H, O, U, R)
- If there are hidden rows with “Filter” menu (CTRL+SHIFT+L)
If no hidden rows the results are as shown below.
If no hidden rows, there is no different result for the SUM, SUBTOTAL 9 and SUBTOTAl 109 functions.
***
The 5th to 8th rows are manually hidden with “Hide Rows” menu (/, H, O, U, R), the results are as shown below.
The SUM function and the SUBTOTAL 9 (include hidden rows) function returns the same results, while the SUBTOTAL 109 (exclude hidden rows) function returns the sum of visible cells only.
***
The 5th to 8th rows are hidden with “Filter ” menu (CTRL+SHIFT+L), the results are as shown below.
The SUBTOTAL 9 and SUBTOTAL 109 functions return the same results, sum only visible cells, while the SUM function keeps the sum visible and invisible numbers.
The Verdict
The SUM function returns the same results regardless of the conditions, no hidden rows, manually hidden rows or “filtered out” hidden rows.
The SUBTOTAL 9 function returns the same result as the SUM function if there are manually hidden rows.
The SUBTOTAL 109 function sum only visible cells, no matter manually hidden rows or “filtered out” hidden rows.
