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.
Auf dieser Seite können Sie das Online-Video Google Sheets SUBTOTAL Function | Calculate Sum, Average, Count, and More | Spreadsheet Tutorial mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer Software Spring 18 Juli 2021 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 20,816 Mal angesehen und es wurde von 93 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
