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.
Nesta página do site você pode assistir ao vídeo on-line Google Sheets SUBTOTAL Function | Calculate Sum, Average, Count, and More | Spreadsheet Tutorial duração hora minuto segundo em boa qualidade , que foi baixado pelo usuário Software Spring 18 Julho 2021, compartilhe o link com seus amigos e conhecidos, no youtube este vídeo já foi visto 21,146 vezes e gostou 95 espectadores. Boa visualização!
