|
<< Click to Display Table of Contents >> SUBTOTAL (calculations without hidden cells) |
Syntax:
SUBTOTAL(FunctionID, Range1 [, Range2, Range3 ...])
Description:
This applies one of the arithmetic functions listed below to one or more cell ranges, ignoring all cells that are currently hidden.
In detail: If you use the commands of 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 will still be included. If you use the SUBTOTAL function, however, 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 filled) |
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) |
If, for example, you set FunctionID to 9, the SUBTOTAL function returns the sum of the specified cell range(s) – but ignores all cells that have been hidden via the commands of the Sort button on the ribbon tab Data | group Filter.
Ignoring manually hidden cells also:
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 filled) |
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) |
If, for example, you set FunctionID to 109, SUBTOTAL returns the sum of the specified cell range(s) – but ignores all cells that have been hidden via the commands of the ribbon tab Data | group Filter.
In addition, all rows will be ignored that were hidden manually via the ribbon command Home | group Cells | Visibility | Hide rows or via the ribbon command Data | group Outliner | Group.
Please note: Only hidden rows will be ignored, while hidden columns will still be included in the calculations.
Example:
SUBTOTAL(9, A1:A10) returns the sum of the range A1:A10, but ignores all cells filtered out by the commands of the ribbon tab Data | group Filter.
See also:
For more information on filters, see Filtering cells by their content.