Subtotal Function in Google Sheets – How to Subtotal in Google Sheets?

The Google Sheets Subtotal function will enable users to subtotal the vertical range of cells using a specified aggregation function. A total of 11+ functions are available in Google Spreadsheet, with the help of which we can perform the subtotal operation. On this page, let us understand how to perform a subtotal function using Google Sheets Tips and Tricks. Read further to find out more.

Subtotal Function Syntax in Google Sheets

The syntax of Google Sheets’ Subtotal function is as follows:

Subtotal function syntax=(function_code, range1, [range2,…])
  • Function_Code: The function code here specifies the function to be used in subtotal aggregation.
  • Range 1: This specifies Range 1 for which the subtotal needs to be calculated.
  • Range 2: Additional ranges which we can specify to calculate the subtotals.
Function Code (includes hidden values) Code (ignores hidden values) Function Code Meaning
AVERAGE 1 101 The AVERAGE function returns the numerical average value in a dataset, ignoring the text.
COUNT 2 102 Returns the number of numeric values in a dataset.
COUNTA 3 103 Returns the number of values in a dataset.
MAX 4 104 Returns the maximum value in a numeric dataset.
MIN 5 105 Returns the minimum value in a numeric dataset.
PRODUCT 6 106 Returns the result of multiplying a series of numbers together.
STDEV 7 107 The STDEV function calculates the standard deviation based on a sample.
STDEVP 8 108 Calculates the standard deviation based on an entire population.
SUM 9 109 Returns the sum of a series of numbers and/or cells.
VAR 10 110 Calculates the variance based on a sample.
VARP 11 111 Calculates the variance based on an entire population.

How to Use the Subtotal Function in Google Sheets?

Let us understand more about the Subtotal function in Google Spreadsheet using an example. Consider the following dataset where we have student grade reports:

Student ID Student Name Major Class Year Midterm Grade Final Grade
N1304 David Computer Science 2012 78 81
N1008 Jason Math 2011 87 80
N1866 Mary Computer Science 2012 79 80
N1774 Rob Computer Science 2012 90 85
N1365 Jason Math 2011 90 96

Now let us perform a few subtotal functions such as AVG, Max, Min, STDEV, SUM, and VAR with the students’ grade reports. The steps to get this done in Google Sheets are as follows:

  • 1st Step: Open the Google Spreadsheet on your device.
  • 2nd Step: Now copy-paste the student grade report for which you want to perform the subtotal function.

SUBTOTAL function in Google Sheets

  • 3rd Step: Next, move to the cell where you want to perform the subtotal function. Here I am creating a dataset for the calculation of subtotals, as shown in the image below.

SUBTOTAL function in Google Sheets

  • 4th Step: Now in the “Result” column, enter the formula as =SUBTOTAL (B10, $F$2: $F$6).
  • 5th Step: Press the “Return” key and you will see the results as follows.
  • 6th Step: Now drag the formula-applied cells to other cell ranges and you will see the subtotals being calculated as shown below.

SUBTOTAL function in Google Sheets

How to Create a Subtotal Function for Hidden or Filtered Data?

Let’s say we have a huge data collection that has been classified and filtered by the department. To review the data in our reports, we have total cells. The purpose is to have the total cells adjusted as an outcome of the filter. So, if we’re looking at data about electronics, the total number of cells must reflect that.

Also, if a row is hidden, we want the data cells to reflect that. The first thing to keep in mind is that the SUM function does not adapt when the data is filtered or hidden. The converse is true with the SUBTOTAL function.

To do this, we select the green filter drop-down at the top of the Department column. Select the department where you would like to show up. You will now see that the data has changed, and many rows are filtered.

You will also notice that the SUM cells in the table below are unchanged. However, the filtered cells that use the subtotal function are adjusted only to show unfiltered cells.

SUBTOTAL function in Google Sheets

Leave a Comment