Google Sheets SUBTOTAL Function Explained (2024 Guide)

SUBTOTAL in Google Sheets Explained

The SUBTOTAL function in Google Sheets is a versatile tool that allows you to perform various kinds of calculations like sum, average, count, and more, on a range of cells while optionally ignoring hidden rows. 

SUBTOTAL Syntax

The basic syntax of the SUBTOTAL function is:

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

• function_code: A number that specifies the operation type (e.g., 1 for AVERAGE, 9 for SUM, etc.).
• range1, [range2, ...]: The range(s) of cells over which the specified operation will be performed. Additional ranges are optional.
• ‍

The SUBTOTAL function uses specific codes to indicate which operation you want to perform (e.g., sum, average, count). Here are some common codes:

1 for AVERAGE

2 for COUNT

3 for COUNTA (counts non-empty cells)

9 for SUM

10 for AVERAGEA (averages including text and false in cells)

11 for MAX

12 for MIN

To adjust these operations to ignore hidden rows, you modify the function code:

• For operations represented by single-digit codes, prepend 10 to the code. For example, 9 (SUM) becomes 109 to calculate the sum while skipping hidden cells.
• For operations represented by two-digit codes, prepend 1 to the code. For example, 10 (VAR) becomes 110 to calculate variance while skipping hidden cells.

How to Use the SUBTOTAL Function in Google Sheets

Let's put this into practice with an example. Assume you have a list of numbers in cells A1 through A10, and you want to sum them up, excluding any hidden rows. In our example, we have ROW 4 hidden. 

Step 1: Select a Cell

Click on the cell where you want the result to appear.

Step 2: Enter the SUBTOTAL Function

Type =SUBTOTAL(109, A1:A10) and press Enter.

As opposed to the SUBTOTAL function, using the SUM function returns a different answer as this includes the hidden cells.

If you wanted to calculate the variance of the same range, excluding hidden rows, you would use:

=SUBTOTAL(110, A1:A10)

We hope that this article has helped you and given you a better understanding of how to use the SUBTOTAL function in Google Sheets. If you enjoyed this article, you might also like our articles on how to create a running total in Google Sheets and how to use NORMDIST in Google Sheets.