How can I use the SUBTOTAL function in Google Sheets?

How can I use the SUBTOTAL function in Google Sheets?

By /

The SUBTOTAL function in Google Sheets is a useful tool that allows users to easily calculate the subtotal of a selected range of cells in a spreadsheet. It can be used to quickly sum up or average a set of data, while also allowing for the exclusion of hidden rows or filtered data. By using the SUBTOTAL function, users can efficiently analyze and organize their data without the need for manual calculations. This function is particularly helpful for creating summaries and reports in a more streamlined and accurate manner. Additionally, it can easily be customized to suit specific needs, making it a versatile tool for various data analysis tasks in Google Sheets.

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

[wpcc-iframe height=”300″ src=”https://docs.google.com/spreadsheet/pub?key=0As3tAuweYU9QdE5zWXl6Q1NOeHk3RGVUX1I0eU9Bamc&output=html” width=”500″]

Cite this article

stats writer (2024). How can I use the SUBTOTAL function in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-subtotal-function-in-google-sheets/

stats writer. "How can I use the SUBTOTAL function in Google Sheets?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-subtotal-function-in-google-sheets/.

stats writer. "How can I use the SUBTOTAL function in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-subtotal-function-in-google-sheets/.

stats writer (2024) 'How can I use the SUBTOTAL function in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-subtotal-function-in-google-sheets/.

[1] stats writer, "How can I use the SUBTOTAL function in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.

stats writer. How can I use the SUBTOTAL function in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.

Download Post (.PDF)

Related terms:

  1. How to Count Cells with Criteria While Ignoring Filters in Google Sheets
  2. How to Calculate Conditional Subtotals with SUBTOTAL and SUMIF in Google Sheets
  3. How to Sum Visible Rows with SUBTOTAL and SUMIF in Google Sheets
  4. How to Count with Criteria in Google Sheets Using SUBTOTAL and COUNTIF
  5. How can I use the SUBTOTAL function to calculate the average of a range of cells in Excel?
  6. How can I use the Subtotal function in VBA? Can you provide examples?
  7. How can I use the SUBTOTAL function in Excel to calculate totals and subtotals for a range of data?
  8. How to Easily Calculate Subtotals in Excel VBA
  9. How can I use SUBTOTAL with SUMPRODUCT in Excel?
  10. How can I use SUBTOTAL with COUNTIF in Excel?
Slide Up
x
PDF
Scroll to Top