|
<< Click to Display Table of Contents >> SUBTOTAL (calculations without hidden cells) |
Syntax:
SUBTOTAL(FunctionID, Range1 [, Range2, Range3 ...])
Description:
Applies one of the arithmetic functions listed below to the specified cell range(s), ignoring all cells that are currently hidden.
In details: If you use the commands from the ribbon tab Data | group Filter to apply a filter to a cell range and then perform arithmetic calculations on this range, cells that have been filtered out are still included. If, however, you use the SUBTOTAL function, filtered cells will be ignored.
Range1, Range2, etc. are the cell ranges you want to evaluate.
FunctionID is a number between 1 and 11 that specifies which arithmetic function to apply:
Value |
Function |
Explanation |
1: |
AVERAGE |
(arithmetic mean) |
2: |
COUNT |
(number of cells filled with numbers) |
3: |
COUNTA |
(number of cells not empty) |
4: |
MAX |
(maximum) |
5: |
MIN |
(minimum) |
6: |
PRODUCT |
(product) |
7: |
STDEV |
(standard deviation of a sample) |
8: |
STDEVP |
(standard deviation of entire population) |
9: |
SUM |
(sum) |
10: |
VAR |
(variance of a sample) |
11: |
VARP |
(variance of entire population) |
For example, if you set FunctionID to 9, SUBTOTAL returns the sum of the specified cell range(s) – ignoring all cells that have been filtered out by the commands of the Sort and filter button on the ribbon tab Data | group Filter.
Ignoring manually hidden cells as well:
There's a second variant of this function. In this case, all rows are ignored that have been hidden manually (using e.g. the ribbon command Home | group Cells | Visibility | Hide rows or the ribbon command Data | group Outliner | Group). To use this variant, simply set the FunctionID to a value between 101 and 111 instead of a value between 1 and 11, as shown in the following table:
Value |
Function |
Explanation |
101: |
AVERAGE |
(arithmetic mean) |
102: |
COUNT |
(number of cells filled with numbers) |
103: |
COUNTA |
(number of cells not empty) |
104: |
MAX |
(maximum) |
105: |
MIN |
(minimum) |
106: |
PRODUCT |
(product) |
107: |
STDEV |
(standard deviation of a sample) |
108: |
STDEVP |
(standard deviation of entire population) |
109: |
SUM |
(sum) |
110: |
VAR |
(variance of a sample) |
111: |
VARP |
(variance of entire population) |
For example, if you set FunctionID to 9, SUBTOTAL returns the sum of the specified cell range(s) – ignoring all cells that filtered out by the commands of the ribbon tab Home | group Filter. In addition, all rows will be ignored that were hidden manually using the ribbon commands Home | group Cells | Visibility | Hide rows and Data | group Outliner | Group.
Please note: Only hidden rows will be ignored, hidden columns are still included in the calculations.
Example:
SUBTOTAL(9, A1:A10) returns the sum of the range A1:A10, ignoring all cells filtered out by the commands of the ribbon tab Home | group Filter.
See also:
For more information on filters, see Filtering cells by their contents.