Hello and welcome to another one of our articles from TeqTog, where we try to provide you with helpful tips and ideas for the daily challenges you might face while working with Google Sheets! We are glad you came here to find out how to use the subtotal function in Google Sheets, so I am going to get right into it.
Before we start, I would like to tell you about our other articles from TeqTog, so do make sure to check them out as well!
Introduction to Google Sheets
First off, let me share what it is like to work with Google Sheets. Google Sheets is an online based spread sheets program just like Microsoft Excel; however, with Google Sheets, you can modify data in real-time while collaborating with other users at the same time as well.
To know better about the differences between Microsoft Excel and Google Sheets: you can go visit TeqTog’s article on Microsoft Excel VS Google Sheets.
Google Sheets is simple, it is fast, it is comforting and it has all the necessary features which allow you to execute your work efficiently. One of those features I mentioned would be the “Subtotal” feature in Google Sheets.
Once you get a good grip on this function, you will find that it is one convenient tool when it comes to reporting your data with versatility, higher comprehensibility and smartly.
Why do you Need the Subtotal Function in Google Sheets?
It is important that you know why you need to apply the Subtotal function instead of the Sum function in Google Sheets, when they both does the same job more or less.
In m example above, I have four cells in which I need the subtotals for each quarter. First, I will add the figures for each quarter using the Sum function and then, add the grand total at the end.
It is very clear that although the totals for each quarter are correct, the grand total is not accurate. This is because, by using the sum function, all of the figures individually have been accumulated in the overall addition and not just the quarter totals.
If I had applied the Subtotal function for each quarter total and then proceeded to apply the same subtotal function for the grand total cell, my results would have been accurate
How to Use the Subtotal Function in Google Sheets
Learning about the Function Code
The Subtotal function in Google Sheets consists of two or more sub-functions. They are the:
- Function Code and
- A minimum of one range to apply the subtotal function on.
To learn about Google Sheet’s function code, you will have to:
- Enter “=Subtotal” into the cell in which you want your subtotals results to be shown.
- Then, a formula such as the following should appear “Subtotal (function_code, range 1, [range 2, …])
- On the bottom left corner of the formula box, you will see the words “Learn More”.
- You have to click on “Learn More” to open a side bar on the right side of your screen. This message box contains a thorough explanation of each function code available.
Here is a summary of each math function code shown in my example above:
- 1 is AVERAGE
- 2 is COUNT
- 3 is COUNTA
- 4 is MAX
- 5 is MIN
- 6 is PRODUCT
- 7 is STDEV (which means standard deviation)
- 8 is STDEVP (this means standard deviation population)
- 9 is SUM
- 10 is VAR (Variance)
- 11 is VARP (Variance Population)
You may check out our full math in Google Sheets articles to know more all math formulas for add, sum, subtract, multiply, divide, square and square root and so on.
In addition, you will have to select a range of cells which will command the subtotal to perform its addition for those specific cells in the given range.
As you can view from my example shown above, I have selected the function code 9, which as we have learnt a little earlier, means Sum (9 is SUM).
Afterwards, I have given the relevant range (January to March). By doing so, I will get the subtotal for the sales revenue for only the first quarter.
In this same way, I may get the subtotals for the rest of the following quarters as well.
In addition to that, if I enter the same Subtotal formula in the cell C18, which is the cell for the grand total, I will get the results for the summation of only the totals of each quarter and the sales revenue for each month will not be considered in the addition.
For getting the Grand Total in my report, I have to type in the usual Subtotal formula and it will read out like this:
=SUBTTOTAL (9, C2: C17)
As you can see, for my range, I have selected all the cells from C2 to C17 (that is straight from January till December). However, my formula will not include any of the figures for any month as I have already applied the Subtotal formula for each quarter total.
Therefore, when I applied the subtotal command on all of the data to get my grand total, the function only considered the results of previously applied subtotal commands.
Function Code for Hidden Cells
I mentioned earlier that there are eleven functions codes for the Subtotal function. Moreover, you can also make a command to Google Sheets to not consider data in any cells which you may have chosen to hide.
To execute the subtotal function and making sure data in hidden cells are ignores; you need to apply the codes I have listed down below:
- 101 is AVERAGE
- 102 is COUNT
- 103 is COUNTA
- 104 is MAX
- 105 is MIN
- 106 is PRODUCT
- 107 is STDEV
- 108 is STDEVP
- 109 is SUM
- 100 is VAR
- 111 is VARP
And there you go. All the basics you need to know how to use the subtotal function in Google Sheets. I hope my simple guidance will be of good help to you!
Best of luck!
You May Love To Read Our Few More Google Sheets Tutorial: