Using the SUBTOTAL function

When working with filtered data, it is important for any formulas or functions to return accurate results. Typically, this isn't a concern because Google Sheets will automatically adjust as data is entered or removed. However, when data is filtered, the function results continue to take into account all of the data, not just the visible data.

In the following example, the AVERAGE function has been used to calculate the average number of books read.

A small spreadsheet showing the grade and number of books read. The average function is in cell B16. The average is 17.84615385.

NOTE: If the function is placed in a row adjacent to the data, the function will be included when the data is filtered or sorted. A simple way to avoid this is to place the function in a non-adjacent row.

When this data is filtered to show only the 11th-grade students, the average remains the same.

The data has been filtered to show just 11th grade students and the blank row. The average is 17.84615385.

Even though they aren't visible, the AVERAGE function is still being applied to cells B2:B14. This is a simple example and it may be immediately obvious that the function isn't calculating just the visible rows. However, with a larger dataset, it may be more difficult to notice discrepancies.

To remedy this problem and calculate the average for just the visible rows, it is necessary to use the SUBTOTAL function.

Understanding the SUBTOTAL function

The SUBTOTAL function can be used to perform other standard functions including AVERAGE, COUNT, and SUM. The SUBTOTAL function differs from other Google Sheets functions because it requires a function code.

The syntax of the Subtotal is:

=SUBTOTAL(function_code, range)

Depending on what calculation you want the SUBTOTAL function to perform, you will use the corresponding function code.

Function	Code
AVERAGE	1
COUNT	2
COUNTA	3
MAX	4
MIN	5
PRODUCT	6
STDEV	7
STDEVP	8
SUM	9
VAR	10
VARP	11

To skip the hidden values, add 10 to the single-digit codes or 1 to the 2-digit codes. For example, the code for AVERAGE would become 101.

By using the SUBTOTAL function to find the average number of books read, hidden values are not calculated. When we filter the data to see only the 11-grade students, the result changes to show the average of the visible values.

The data has been filtered to show only the 11-grade students. In cell B15, the subtotal function =subtotal(101,b2:b14) returns the value 12.25.

Copying the practice workbook

If you are beginning this course at this point, you will need to copy the practice workbook to your Google Drive.

NOTE: If you have been working through the course, your file should be up to date and you will not need to copy the practice document. You may continue at Entering the SUBTOTAL function.

To begin copying the practice workbook in a new browser window,

Right-click Copy practice workbook, Click Open link in new window

The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.

Move to the new browser window.
To copy the workbook file, in the browser window,

Click

A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.

Exploring the data

Before we begin working with the SUBTOTAL function, let's take a moment to become familiar with our worksheet.

NOTE: If you have been working through this course from the beginning, it is not necessary to open a new file.

To open the workbook,
Double-click Copy of Google Sheets_Sorting and filtering_Using the SUBTOTAL function

This workbook contains one worksheet, Participants. This is a list of students who participated in a local summer reading program. In previous sections, Sorting data in Google Sheets and Filtering data in Google Sheets, we have answered several questions about our data. At this point, the data is not sorted or filtered in any way.

Entering the SUBTOTAL function

Let's use the SUBTOTAL function to calculate the total number of books read by elementary school students. To avoid the function being filtered or sorted with the data, we will enter it in cell H50. Then we will filter the data to see only the young students.

The function code for SUM is 109.

To correctly place the cursor,
Click in cell H50
To enter the SUBTOTAL function, type:
=subtotal(109, H2:H48) Enter key

The total number of books read by all participants, 886, appears in cell H50.

Let's filter the data to see only the elementary school students.

To open the filter dialog box,
Click
To clear the currently selected schools,
Click
To select the elementary schools,
Click Carmichael Elementary, Click Newton Elementary, Click Wilkins Elementary
To close the dialog box and filter the data,
Click

The number of books read by elementary school students, 396, is now in cell H50.

The filtered spreadsheet showing the number of books read by elementary school students.

Using the SUBTOTAL function

Understanding the SUBTOTAL function

Copying the practice workbook

Copy the practice workbook to Google Drive.

Exploring the data

Step 1. Open the workbook, Copy of Google Sheets_Sorting and filtering_Using the SUBTOTAL function, if necessary.

Entering the SUBTOTAL function

Step 1. Enter the SUBTOTAL function in cell H50.

Step 2. Filter the data.