Excel SUBTOTAL Function
The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.
- function_num - A number that specifies which function to use in calculating subtotals within a list. See table below for full list.
- ref1 - A named range or reference to subtotal.
- ref2 - [optional] A named range or reference to subtotal.
Use the SUBTOTAL function to get a subtotal in a list or database. SUBTOTAL has the ability to use a variety of functions when subtotaling, including AVERAGE, COUNT, MAX, and others (see table below for a complete list). By default, SUBTOTAL excludes values in rows hidden by a filter, as explained below. This makes SUBTOTAL very useful in Excel Tables.
Below are a few examples of SUBTOTAL configured to SUM, COUNT, and AVERAGE the values in a range. Notice the only difference is the value used for the function_num argument:
SUBTOTAL behavior is controlled by the function_num argument, which is provided as a numeric value. The table below shows available functions:
|Function||Include hidden||Ignore hidden|
Manually hidden values
Notice the values used for the function_num argument are "paired" (e.g. 1-101, 2-102, 3-103, ...). This is related to how SUBTOTAL deals with manually hidden rows. When function_num is between 1-11, SUBTOTAL includes cells that have been manually hidden. When function_num is between 101-111, SUBTOTAL ignores values in rows that have been manually are hidden.
SUBTOTAL always ignores values in cells are hidden with a filter. Values in "filtered out" rows are not included, regardless of function_num.
- When function_num is between 1-11, SUBTOTAL includes values that are hidden
- When function_num is between 101-111, SUBTOTAL excludes values that are hidden
- In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.
- SUBTOTAL ignores other subtotals that exist in references are ignored to prevent double-counting
- SUBTOTAL is designed to work with vertical data values arranged vertically. In Horizontal ranges, values in hidden columns are always included.