SUBTOTAL function. Aggregate only the values displayed.(Microsoft Excel)
Aggregate only the values displayed.
Aggregate functions, such as the SUM function, include all cells that are hidden by the filter.
However, the SUBTOTAL function excludes hidden cells from the total.
Contents
How it works
=SUBTOTAL(Function_num,ref 1 to 254)
| Name | Omission | Specify |
|---|---|---|
| Function_num | Required. | Aggregation Method. See below for details. |
| ref 1 to 254 | Only 1 is required. | Specify a range of cells to be aggregated. |
Function_num
Cells that are hidden by the filter are always excluded from the tally.
However, whether or not cells that are hidden by formatting are excluded depends on the value of the setting.
| Aggregation Method. (Include hidden.) | Aggregation Method. (Exclude hidden.) | Function |
|---|---|---|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | Count of non-blanks.COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | Sample standard deviation.STDEV |
| 8 | 108 | Standard deviation.STDEV.P |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
Example Results
It is mainly used for tables that use filter.
Cell C2 is a SUM function in the same cell range as the SUBTOTAL function.
Some cells were hidden by the filter.
The result of the SUBTOTAL function is now only the displayed cells, but the result of the SUM function remains the same.
This function can be applied to a range of cells.
---