Google Sheets SUBTOTAL Function | Calculate Sum, Average, Count, and More | Spreadsheet Tutorial
The Google Sheets SUBTOTAL function returns the subtotal for a vertical range of cells in a spreadsheet, based on the specified aggregation function code. The aggregation functions for SUBTOTAL range from AVERAGE to MAX to VAR functions.
In essence, using only the SUBTOTAL function, you can get data like average, count, maximum, sum, or standard deviation of data in one or more ranges.
Here's the format of the Google Sheets SUBTOTAL function formula:
=SUBTOTAL(function_code, range1, [range2], …)
The equal-to sign indicates that you are typing a formula, a function
formula in this case.
SUBTOTAL is the name of the function.
function_code is the aggregation function code. Here are the codes
and the function they represent.
Function Function Code
AVERAGE 1
COUNT 2
COUNTA 3
MAX 4
MIN 5
PRODUCT 6
STDEV 7
STDEVP 8
SUM 9
VAR 10
VARP 11
If you do not want any hidden data to be considered by a particular function, prefix its code with 10, if the code is single digit, and with 1, if the
code is double-digit. Example: 102 for COUNT function and 110 for
VAR function. Both of these codes will skip hidden data.
Let's look at an example of the SUBTOTAL function formula:
=SUBTOTAL(1, B7:B11)
The above function returns the average of the numbers in the range B7 to B11, because the function code is 1, which corresponds to the AVERAGE function.
If you want, for example, the sum of numbers in the range, change the function code to 9.
Take a look a this video tutorial, which gives the steps to use the Google Sheets SUBTOTAL function with an example.
On this page of the site you can watch the video online Google Sheets SUBTOTAL Function | Calculate Sum, Average, Count, and More | Spreadsheet Tutorial with a duration of hours minute second in good quality, which was uploaded by the user Software Spring 18 July 2021, share the link with friends and acquaintances, this video has already been watched 21,146 times on youtube and it was liked by 95 viewers. Enjoy your viewing!
