Returns a subtotal for a vertical range of cells using a specified aggregation function.
SUBTOTAL(function_code, range1, [range2, ...])
function_code- The function to use in subtotal aggregation.
Hidden values can be skipped for any of these codes by prepending
10(to the single-digit codes) or
1(to the 2-digit codes). e.g. 102 for
COUNTwhile skipping hidden cells, and
VARwhile doing so.
range1- The first range over which to calculate a subtotal.
range2, ...- Additional ranges over which to calculate subtotals.
Cells that are hidden due to autofilter criteria are never included in
SUBTOTAL, irrespective of the
Cells within any of the specified
rangearguments that contain
SUBTOTALcalls are ignored to prevent double-counting.
SUBTOTALcan be used to created dynamic dashboards by having the function code argument refer to another cell. When combined with list-based data validation, this cell can become a drop-down list that instantly updates the entire dashboard.
SUBTOTALcan be used for quick analysis of different subsets of data by building a subtotal dashboard above a filtered region. Each time the filter criteria change, the dashboard will automatically update with new aggregations.
SUBTOTALhelps prevent double-counting associated with simple
VARP: Calculates the variance based on an entire population.
VAR: Calculates the variance based on a sample.
SUM: Returns the sum of a series of numbers and/or cells.
STDEVP: Calculates the standard deviation based on an entire population.
STDEV: The STDEV function calculates the standard deviation based on a sample.
PRODUCT: Returns the result of multiplying a series of numbers together.
MIN: Returns the minimum value in a numeric dataset.
MAX: Returns the maximum value in a numeric dataset.
Returns the number of values in a dataset.
Returns the number of numeric values in a dataset.
AVERAGE: The AVERAGE function returns the numerical average value in a dataset, ignoring text.