SUBTOTAL function

Returns a subtotal for a vertical range of cells using a specified aggregation function.

Sample Usage

SUBTOTAL(1,A2:A5,B2:B8)

Syntax

SUBTOTAL(function_code, range1, [range2, ...])

  • function_code - The function to use in subtotal aggregation.

    • 1 is AVERAGE

    • 2 is COUNT

    • 3 is COUNTA

    • 4 is MAX

    • 5 is MIN

    • 6 is PRODUCT

    • 7 is STDEV

    • 8 is STDEVP

    • 9 is SUM

    • 10 is VAR

    • 11 is VARP

    • 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 COUNT while skipping hidden cells, and 110 for VAR while doing so.

  • range1 - The first range over which to calculate a subtotal.

  • range2, ... - Additional ranges over which to calculate subtotals.

Notes

  • Cells that are hidden due to autofilter criteria are never included in SUBTOTAL, irrespective of the function_code used.

  • Cells within any of the specified range arguments that contain SUBTOTAL calls are ignored to prevent double-counting.

  • SUBTOTAL can 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.

  • SUBTOTAL can 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.

  • Using SUBTOTAL helps prevent double-counting associated with simple SUM formulas.

See Also

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.

COUNTA:

Returns the number of values in a dataset.

COUNT:

Returns the number of numeric values in a dataset.

AVERAGE: The AVERAGE function returns the numerical average value in a dataset, ignoring text.

Examples

[フレーム]
true
Visit the Learning Center

Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.