Skip to Content

SUBTOTAL Function in Google Sheets

The SUBTOTAL function is a versatile function that is extremely useful when working with data.

This is not a very well-known function, but by the end of this guide, you will see just how useful it really is.

In this tutorial, I will show you how to use the SUBTOTAL function in Google Sheets

Table of Contents

SUBTOTAL Function Syntax

The SUBTOTAL function returns a subtotal for a range of cells using the aggregation function that is specified.

The syntax of the SUBTOTAL function is:

=SUBTOTAL(function_code, range1, [range2, …])

  • function_code – this is the aggregation function that you want to use to get your subtotal. You have to put in the code of the aggregation function. Here are the codes
    • 1 – AVERAGE
    • 2 – COUNT
    • 3 – COUNTA
    • 4 – MAX
    • 5 – MIN
    • 6 – PRODUCT
    • 7 – STDEV
    • 8 – STDEVP
    • 9 – SUM
    • 10 – VAR
    • 11 – VARP
      • One important thing to note is that when using these codes is that you can skip hidden values by placing a 10 before the single-digit codes and a 1 before the 2-digit-codes (101 (AVERAGE), 111 (VARP))
  • range1 – this is the range that will be used to calculate a subtotal
  • range2 – this is optional, you can add additional ranges here

There are some other important details that you need to know about how the SUBTOTAL function works:

  • Autofiltered rows that are hidden are never used in SUBTOTAL calculations
  • Cells that feature the SUBTOTAL function are ignored from SUBTOTAL calculations (this can help prevent double-counting)

Using the SUBTOTAL Function

There are many different ways you can use this function to group and aggregate data depending on what you are trying to look at.

Here is a basic example of how the SUBTOTAL function might be used in a spreadsheet:

SUBTOTAL example

You can see in the example above that by using this one function I can quickly aggregate my data with multiple functions. You just have to change the first argument of the function (function_code) to quickly return different data points that you can use in the analysis.

One very useful feature of this function is that it does not use any cells featuring the SUBTOTAL function in its calculation. This can be very useful in preventing double counting.

For example, look at how this is used in this dataset:

SUBTOTAL SUM example

In the example above, each of the cells highlighted yellow features a SUBTOTAL formula using the SUM aggregation function. You can see that in the example above, the grand total formula is set to SUBTOTAL SUM the entire range of C2:C11. However in the resulting total C5 and C11 (my subtotals) are ignored from the grand total, because they feature SUBTOTAL formulas.

This is an example of how this function is useful in preventing double counting. You can use it in place of the standard functions like SUM to prevent double-counting when creating subtotals.

Closing Thoughts

In this tutorial, I have covered the basics of this function, but there are certainly more advanced ways that it can be used. You can use this function to create dynamic dashboards with drop-down lists where the function code changes to display whatever aggregation method is desired.

You can use it to make a quick analysis of different subsets of data by filtering in combination with this function. First take the time to master the basics of using this in your formulas, and then you can begin to play with more advanced ways of using it.

More Google Sheets Tutorials:
How to Calculate Percentage
How to Calculate Weighted Average
Return Unique Values Across Multiple Columns
How to Use the PMT Function