SUBTOTAL is one of the default functions of Microsoft Excel. This function provides the opportunity to perform a group of Excel function operations. We can apply different Excel functions by inserting this function. In this article, we will discuss how to use this Excel SUBTOTAL function with examples and proper illustrations.
This is an overview of the SUBTOTAL function. In the below sections we will describe how to use this function in detail.
Introduction to SUBTOTAL Function
The SUBTOTAL function returns a list or database. It’s in Math/Trig function category. It offers 11 Excel functions to perform by inserting the corresponding argument.
Syntax:
SUBTOTAL(function_num,ref1,[ref2],…)
| Argument | Required/Optional | Explanation |
|---|---|---|
| function_num | Required | This number ranges from 1 to 11 or 101 to 111 specifies which function will be used for the SUBTOTAL. 1-11 includes manually hidden rows, while 101-111 excludes them; filtered-out cells are always excluded. |
| ref1 | Required | This is the first named range, which will be used in the function. |
| ref2 | Optional | We can use 2 to 254 ranges or references. |
Return:
This function returns any numeric value.
Available in:
Microsoft Excel 365, Microsoft Excel 365 for Mac, Excel for the web Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2019 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel for Mac 2011, Excel Starter 2010.
Calculations Available with SUBTOTAL Function
The SUBTOTAL function can perform 11 different Excel operations or functions. These functions are specified in the function_num argument. It also offers two conditions for every function. One is including hidden and filtered data and the other is ignoring the hidden data. The available functions with their corresponding numbers are given below:
| Function | With Hidden Data | Without Hidden Data |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV.S | 7 | 107 |
| STDEV.P | 8 | 108 |
| SUM | 9 | 109 |
| VAR.S | 10 | 110 |
| VAR.P | 11 | 111 |
How to Use SUBTOTAL Function in Excel: 3 Suitable Examples
Now, we show the use of the SUBTOTAL Function. For this, we consider a data set of clothing stores from two branches.
Apply SUBTOTAL from the Data Tab:
Step 1:
- First, sort the data in Column B.
Step 2:
- Go to the Data tab.
- Select Subtotal from the Outline command.
Step 3:
- Get the Subtotal dialog box.
- Select the column from the dropdown.
Step 4:
- Select the required function from the Use function.
Step 5:
- Now, choose the columns where the function will perform.
Step 6:
- Then, press OK.
Here, we apply the sum operation based on the product category. We also get some for each product and finally also get the sum for all the products.
Step 7:
- In the left upper corner, 1,2,3 numbers are shown.
- First, click on 1.
Here, we get only the Grand Total.
Step 8:
- Then, select 2.
We get the Grand Total including the total of each product separately.
Step 9:
- Finally, select 3.
Here, we get each product with details separately with the totals.
This method is applied when we want to perform the SUBTOTAL with a detailed operation. We can also simply use the formulas as per our requirements.
Apply SUBTOTAL by Inserting Formula:
Step 1:
- Go to Cell C17.
- Write the SUBTOTALÂ function.
- In the first argument, a dropdown will appear.
- Choose the required function or operation from the dropdown.
Step 2:
- Then select the data or range to apply the function.
- Here, our applied formula is:
=SUBTOTAL(9,D5:D14)
Step 3:
- Now, press Enter.
Here, we performed the sum operation using the SUBTOTAL function.
How to Use SUBTOTAL Function in Excel: 3 Examples
Here, we will see different uses of the Excel SUBTOTAL Function.
1. Calculate the Average Using Excel SUBTOTAL Function
We will find the average inserting the SUBTOTAL function in this method.
Step 1:
- First, modify the data set to perform the average operation.
Step 2:
- Write the SUBTOTAL Function on Cell C17.
- We put 1 on the first argument, which indicates average.
- So, the formula becomes:
=SUBTOTAL(1,D5:D14)
Step 3:
- Then press Enter.
Here, we get the average price by applying the SUBTOTAL function.
2. Count Operation in Excel by SUBTOTAL
In this section, we will show the count operation by the SUBTOTAL function. We can show the COUNT and COUNTA operations of Excel through this SUBTOTAL default function. The COUNT function counts only the numeric values. But COUNTA counts both numeric and alphabetic values.
Step 1:
- We modify the data before applying the operation.
Step 2:
- We apply the formulas in Cells C17 and C18Â individually.
Step 3:
- Now, put the formula to perform the COUNT operation in Cell C17.
=SUBTOTAL(2,D5:D14)- Write the formula to perform the COUNTA operation in Cell C18.
=SUBTOTAL(3,D5:D14)- Then press Enter.
We can see the results here. In the formula 2 indicates COUNT and 3 indicates COUNTA operation.
3. Insert SUBTOTAL Function to Find Max/Min Value
In this section, we will apply the SUBTOTAL function to find out the maximum or minimum value.
Step 1:
- We mention two separate boxes to show the maximum and minimum values.
Step 2:
- Now, put the formula on Cell C17. The formula is:
=SUBTOTAL(4,D5:D14)- Here, 4 is indicating the maximum value.
Step 3:
- Now, press Enter.
Step 4:
- Again, write a formula on Cell C18 to get the minimum value. The formula is:
=SUBTOTAL(5,D5:D14)- Here, 5 is indicating the minimum value.
Step 5:
- Then press Enter.
Here, we get both the maximum and minimum values by using the SUBTOTAL function.
The SUM operation is already shown. We can also perform PRODUCT, Standard Deviation, and Variance operations through this SUBTOTAL function. Including all, we can perform 11 operations through this SUBTOTAL function.
Things to Remember
When we need to avoid the hidden or filtered object, we will use 101-111 in the first argument.
Download Practice Workbook
Download the following practice workbook to exercise while you are reading this article.
Conclusion
In this article, we described how to use the Excel SUBTOTAL function. I hope this will satisfy your needs. Please give your suggestions and feedback in the comment box.
