When working in Google Sheets, you may want to periodically check the subtotal of your data set or include results in the final documentation.
The SUBTOTAL function is the easiest way to accomplish this. On top of calculating subtotals for data lists, the subtotal function also:
 Calculates metrics with hidden or filtered data rows
 Dynamically selects functions
This is an underutilized function due to its tricky nature. Itâ€™s not exactly beginnerfriendly due to having to use function controls to operate. However, if you follow this guide, you should be fluently working with the SUBTOTAL function in no time.
What is a Function Control?
Function controls tell a particular function in Google Sheets how to behave. In the case of the SUBTOTAL function, you may want to find the SUM, AVERAGE, MAX, etc.
Using function control code in the syntax tells the SUBTOTAL function which calculation it should be using.
It may sound a little confusing, but the cheat sheet below will help you. You can check back to it while youâ€™re working on your spreadsheet. There is no need to memorize the function controls straight away.
You could even create a dynamic function selectorÂ so you donâ€™t have to memorize each code.
SUBTOTAL Function Controls
Formula used 
Code (including hidden values) 
Code (excluding hidden values) 
Average 
1 
101 
Count 
2 
102 
Counta 
3 
103 
Max 
4 
104 
Min 
5 
105 
Product 
6 
106 
Standard Deviation 
7 
107 
Standard Deviation Population 
8 
108 
Sum 
9 
109 
Variance 
10 
110 
Variance Population 
11 
111 
As you can see in the table, there are eleven function controls for the SUBTOTAL function in Google Sheets. Each of these function controls can either include or exclude hidden values.
Onedigit or twodigit function controls include hidden values. Three digits in the function control argument mean the hidden values are excluded.
Letâ€™s take a look at how the function controls fit into the syntax.
SUBTOTAL Syntax
=SUBTOTAL(function_code, range1, [range2, ...])
Youâ€™ll need a minimum of two arguments for the SUBTOTAL function to work. The first is one of the function controls listed above and the second is the range of data.
However, you can add as many data ranges as youâ€™d like to this function.
Letâ€™s use the following arguments in the syntax as an example.
=SUBTOTAL(9, B2:B7)
Since the first argument in the SUBTOTAL function is a control, it tells the function to apply the desired aggregation. In this example, 9 is the chosen function control.
If you look at the above table, you can see that using 9 in the syntax will apply the SUM function to the given range of B2:B7, including any hidden values.
To exclude any hidden values, you would have to use the equivalent threedigit function control of 109 instead.
In the next screenshot, you can see we added a second set of data to the subtotal.
This will include the set of data from A2:A7 in the calculation too.
Note that the square brackets â€œ[â€œ given in the sample syntax are not necessary and will cause an error if entered into the formula. They just signify the start and finish of the ranges you could enter in the syntax and are not actually part of the calculations.
Why You Should Use the SUBTOTAL Function
It may seem easier to use the standard builtin function for each subtotal calculation instead. After all, in our first example, you could have just as easily used the syntax of =SUM, (B2:B7) and still received the correct answer.
While this is true, itâ€™s the more complex spreadsheets that would benefit from using the SUBTOTAL function instead.
In the following example, the SUM function has been used to calculate the subtotals and grand total on the spreadsheet.
You can see that the subtotals are correct using the SUM function, but the grand total is wrong. This is because it calculated the subtotal figures as well as the other numbers in the column that created the subtotals.
You could avoid this by using the syntax for the SUM function of =SUM(C9+C18+C27) to get the correct answer. But, this is a much slower and more tedious way than using the SUBTOTAL function in the first place. Itâ€™s also much easier to make a mistake by manually clicking or typing each field.
Hereâ€™s how it looks using the SUBTOTAL function for the same dataset.
You can see that the grand total now has the correct answer of 70. This is because the SUBTOTAL function ignores the duplicate data while the SUM function does not. It does so by ignoring every other SUBTOTAL function in the given range.
This concept applies to every comparable function control used by the SUBTOTAL function, not just SUM.
Interactions of the SUBTOTAL Function with Filtered and Hidden Data
Take a look at the following data set.
In it, we have used the following formulas in each of these cells.
 C15 =SUM(C2:C13)
 C16 =SUBTOTAL(9,C2:C13)
 C17 =SUBTOTAL(109,C2:C13)
As you can see, each of these formulas gives the same correct answer for the total number of sales. But, when you start filtering or hiding data, each formula will interact differently with the spreadsheet.
Filtered Data
In this next example, we have filteredÂ by brand to only show the sales of one.
You can see that both of the SUBTOTAL rows have removed any filtered data from their calculations but the SUM function has not. This same concept would apply if we filtered the year column. This would have given the subtotal by date instead.
So, make sure you choose the right function as to whether or not you want to include filtered data in the totals of your spreadsheet.
Hidden Data
For the last example, we added a hidden row to the data set too. Notice that the figure in cell C17 changes. This is because we used the function control of 109, which ignores hidden rows. The same would apply to hidden columns.
Make sure you pick the right function control and subtotal with condition parametersÂ to apply to the end goal of your spreadsheet.
Conclusion
At first glance, the SUBTOTAL function can be a little intimidating. Yet, once you wrap your head around the function controls, you will save a ton of time while working on your spreadsheets.
If you found this tutorial on how to count unique subtotals in Google Sheets useful, you may also be interested in learning about running totals.
Chris Daniel
Chris is a spreadsheet expert and content writer from Australia.
Find more of his work on WriterAccess

Chris Daniel#molonguidisabledlink

Chris Daniel#molonguidisabledlink