What Is SUBTOTAL Function In Google Sheets?
SUBTOTAL function in Google sheets, as the name suggests is an inbuilt function used to find the subtotal of data. In this formula, we need to add a code number which signifies the function to be used. For instance, if we want to find the average of a data, then, we need to use the code value 1. Similarly, we want to use the SUM formula, we need to use the code value 9.
For example, consider the below table showing sales in 4 different regions.
Now, let us use the SUBTOTAL function to find the SUM of the values.
To begin with, insert the data in the spreadsheet. In this example, the data is available in the cell range A1:B5. Next, select the cell where we want to find the result. In this example, it is cell B7. Insert the SUBTOTAL function in cell B7. Include 9 as the first argument which is the code value that indicates to perform SUM. So, the complete formula is =SUBTOTAL(9,B2:B5). Press Enter key. We can see the result immediately in cell B7.
In this article, let us learn how to use the SUBTOTAL function in Google sheets with detailed examples.
Table of contents
Key Takeaways
- SUBTOTAL function in Google sheets helps users find the result for a given data using other primary functions.
- Before using SUBTOTAL function, we need to specify the right code number, designated for each function.
- The syntax of Google sheets SUBTOTAL function is =SUBTOTAL(code,range1,…) where,
- code indicates the code number to be used to include a function
- range1 shows the cell range to which users need to find the result.
- We can include as many cell range as we want. But code and range1 are the two mandatory arguments.
- This function is highly used in finance and banking sectors, to avoid errors.
SUBTOTAL() Google Sheets Formula
The formula or syntax of SUBTOTAL function in Google sheets is
=SUBTOTAL(code,range1,…)
where,
- code is the number code that indicates the function to be used
- range1 is the cell range or cell references for which we want to find the result.
Both the arguments are mandatory.
How To Use SUBTOTAL() Function Google Sheets?
To use the SUBTOTAL function, we need to know the numeric code, which is the mandatory first argument that signifies the function to be used.
The code values for some of the most used Google sheets functions are as follows:
- AVERAGE – 1
- COUNT – 2
- COUNTA – 3
- MAX – 4
- MIN – 5
- PRODUCT – 6
- STDEV – 7
- STDEVP – 8
- SUM – 9
- VAR – 10
- VARP – 11
Steps To Use SUBTOTAL Function In Google Sheets:
Now, let us learn the steps to be used while using SUBTOTAL Function in Google Sheets.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. Next, select the cell where we want to find the result.
Step 2: Insert the SUBTOTAL function in cell.
Include the code as the first argument which is the code value that indicates to perform specific function.
Step 3: Press Enter key.
We can see the result immediately in the active cell.
Likewise, we can use the SUBTOTAL function to find desired results.
Examples
Now, let us learn how to use SUBTOTAL function with detailed examples in Google Sheets.
Example #1 – Using The SUBTOTAL Function For Filtered Or Hidden Data
For example, consider the below table showing sales in different regions in columns A,B and C, respectively.
Now, assume the sales value in North and East has to be hidden, as shown in the below image.
Let us use the SUBTOTAL function to find the SUM of the values.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is available in the cell range A1:C8. Next, select the cell where we want to find the result. In this example, it is cell B9.
Step 2: Insert the SUBTOTAL function in cell B9.
Include 9 as the first argument which is the code value that indicates to perform SUM.
So, the complete formula is =SUBTOTAL(9,B4:B8)
Step 3: Press Enter key.
We can see the result immediately in cell B9, as shown in the below image.
Now, note that the SUM of values in cells B4,B5,B8 will only be 1100, as shown in the below image.
Therefore, it is evident that by using SUBTOTAL function, we can find the result in hidden or filtered rows also.
Likewise, we can use the SUBTOTAL function to find desired results.
Example #2 – Using The SUBTOTAL Function To Create Subtotals
For example, consider the below table showing sales in different regions in columns A,B and C, respectively.
Now, let us find the total sales in North and South regions.
The steps to find the total sales in North region are:
Step 1: To begin with, select the cell where we want to find the result. In this example, it is cell B5.
Step 2: Insert the SUM function in cell B5.
So, the complete formula is =SUM(B2:B5)
Step 3: Press Enter key.
We can see the result immediately in cell B5, as shown in the below image.
Likewise, we can see the result.
SOUTH REGION RESULTS
Similarly, let us find the total sales in South region.
The steps to find the total sales in South region are:
Step 1: To begin with, select the cell where we want to find the result. In this example, it is cell B8.
Step 2: Insert the SUM function in cell B8.
So, the complete formula is =SUM(B6:B7)
Step 3: Press Enter key.
We can see the result immediately in cell B8, as shown in the below image.
Now, we have found the total for both North and South regions.
Now, let us find the total sales using SUM function.
The steps to find the total sales are:
Step 1: To begin with, select the cell where we want to find the result. In this example, it is cell B10.
Step 2: Insert the SUM function in cell B10.
So, the complete formula is =SUM(B2:B8)
Step 3: Press Enter key.
We can see the result immediately in cell B8, as shown in the below image.
Now, let us use SUBTOTAL function to find the total sales.
The steps to find the total sales using SUBTOTAL function are:
Step 1: To begin with, select the cell where we want to find the result. In this example, it is cell B11.
Step 2: Insert the SUM function in cell B11.
So, the complete formula is =SUM(9,B2:B4,B6:B7)
Step 3: Press Enter key.
We can see the result immediately in cell B11, as shown in the below image.
Therefore, it is evident that by using SUBTOTAL function, we can find the result without duplicate values.
Likewise, we can use the SUBTOTAL function to find desired results.
Example #3 – Create A Dynamic Function Selector With A SUBTOTAL Formula In Google Sheets
Users can create a dynamic function selector to use SUBTOTAL function in Google sheets.
To do this, we need to first create a dynamic function selector.
The steps to create dynamic function selector are:
Step 1: Select a cell. Click on Data and choose Data Validation.
Step 2: The Data validation rules window appears on the right-end of the screen.
Step 3: Click on Add Rule and enter the formulas in the criteria by clicking on Add another rule option.
For this example, we have entered Average and SUM functions, as shown in the below image.
Click on the drop-down option. We will be able to see the functions.
Now, instead of manually typing the formula or code, we can simply use the dynamic function selector.
Applications Of SUBTOTAL Function In Financial Analysis
- SUBTOTAL function is used to find in financial sector as this function saves time.
- The results are also comparatively error-free as the function by default, does not include duplicate values.
- Similarly, the SUBTOTAL function also includes hidden or filtered data while using functions between the code 1 and 11.
Important Things To Note
- SUBTOTAL function in Google sheets helps users find the desired result in any data using the code value.
- Users must include the code designated for each function.
- Using this function, we can find result in hidden or filtered data also.
Frequently Asked Questions (FAQs)
For example, consider the below table showing sales in 4 different regions.
Now, let us use the SUBTOTAL function to find the SUM of the values.
The steps are:
Step 1: To begin with, insert the data in the spreadsheet. In this example, the data is available in the cell range A1:B5. Next, select the cell where we want to find the result. In this example, it is cell B7.
Step 2: Insert the SUBTOTAL function in cell B7.
Include 9 as the first argument which is the code value that indicates to perform SUM.
Step 3: Press Enter key.
We can see the result immediately in cell B7.
Likewise, we can use the SUBTOTAL function to find desired results.
In Google sheets, there are codes for almost all the functions. Between 1 and 11, Google sheets has designated all the important, most commonly used functions such as AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR and VARP functions.
SUBTOTAL function is highly used to direct the functions is specific cell ranges. There is a chance of double calculation if other functions like SUM, Average are used. But SUBTOTAL function ensures no values are calculated twice.
Download Template
This article must help understand SUBTOTAL Function In Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is a SUBTOTAL Function In Google Sheets. We explain how to use SUBTOTAL Function in Google Sheets with examples. You can learn more from the following articles. –
Leave a Reply