The SUBTOTAL function is a default feature in Microsoft Excel that allows you to perform a group of Excel function operations. By inserting this function, you can apply various Excel functions. In this article, we’ll explore how to use the Excel SUBTOTAL function with examples and proper illustrations.
Introduction to SUBTOTAL Function
The SUBTOTAL function returns a list or database and falls under the Math/Trig function category. It offers 11 Excel functions that can be executed by specifying 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 the SUBTOTAL will use. Numbers 111 includes manually hidden rows, while 101111 exclude them. Filteredout cells are always excluded. 
ref1  Required  The first named range to be used in the function. 
ref2  Optional  You can use 2 to 254 ranges or references. 
Return
The SUBTOTAL 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 the SUBTOTAL Function
The SUBTOTAL function can perform 11 different Excel operations or functions. These functions are specified using the function_num argument. It also offers two conditions for each function: including hidden and filtered data or ignoring the hidden data. Here are the available functions along with their corresponding numbers:
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 
Let’s demonstrate how to use the SUBTOTAL function with a data set from two clothing store branches.
Using the SUBTOTAL
 Sort the Data:
 First, sort the data in Column B.
 Access the Data Tab:
 Go to the Data tab.
 Select Subtotal:
 Choose Subtotal from the Outline command.
 Configure the Subtotal Dialog Box:
 Select the desired column from the dropdown.

 Choose the function you want to apply.

 Specify the columns where the function should perform.
 Press OK:
 Confirm your settings.
By following these steps, you can apply the sum operation based on the product category. You’ll obtain subtotals for each product and a grand total for all products.
 Clicking on 1 in the upper left corner provides only the Grand Total.
 Selecting 2 includes the total of each product separately.
 Finally, choosing 3 displays each product with individual details and totals.
This method is useful when you need a detailed subtotal operation. Alternatively, you can use formulas as per your specific requirements.
Applying the SUBTOTAL by Inserting Formula
 Navigate to Cell C17:
 Go to Cell C17.
 Enter the SUBTOTAL Function:
 In Cell C17, enter the SUBTOTAL function.
 In the first argument, a dropdown will appear.
 Choose the required function or operation from the dropdown.
 Select the Data or Range:
 Next, select the data or range to which you want to apply the function.
 The applied formula in our example is:
=SUBTOTAL(9,D5:D14)
 Press Enter:
 Press Enter to execute the sum operation using the SUBTOTAL function.
Here, we performed the sum operation using the SUBTOTAL function.
Method 1 – Calculate the Average Using Excel’s SUBTOTAL Function
To find the average, follow these steps:
 Modify the Data Set:
 First, modify the data set to perform the average operation.
 Enter the SUBTOTAL Function:
 In Cell C17, enter the SUBTOTAL function.
 Use 1 as the first argument to indicate the average.
 The formula becomes:
=SUBTOTAL(1,D5:D14)
 Press Enter:
 Press Enter to obtain the average price using the SUBTOTAL function.
Here, we get the average price by applying the SUBTOTAL function.
Method 2 – Count Operation in Excel Using SUBTOTAL
In this section, we’ll demonstrate how to perform the count operation using the SUBTOTAL function in Excel. The SUBTOTAL function allows us to display both the COUNT and COUNTA operations. Specifically:
 The COUNT function counts only numeric values.
 The COUNTA function counts both numeric and alphabetic values.
Step 1: Data Modification
 Before applying the operation, make any necessary modifications to the data.
Step 2: Applying Formulas
 Apply the following formulas individually to cells C17 and C18:

 To perform the COUNT operation:
=SUBTOTAL(2,D5:D14)

 To perform the COUNTA operation:
=SUBTOTAL(3,D5:D14)

 Press Enter after entering each formula.
Results: The formula with argument 2 indicates the COUNT operation, while the formula with argument 3 indicates the COUNTA operation.
Method 3 – Using SUBTOTAL to Find Maximum and Minimum Values
In this section, we’ll use the SUBTOTAL function to determine the maximum and minimum values.
Step 1: Separate Boxes for Maximum and Minimum Values
 Create two separate cells to display the maximum and minimum values.
Step 2: Maximum Value Formula
 Enter the following formula in cell C17 to find the maximum value:
=SUBTOTAL(4,D5:D14)
 Here, the argument 4 corresponds to the maximum value.
Step 3: Press Enter
 Press Enter to calculate the maximum value.
Step 4: Minimum Value Formula
 Next, enter the following formula in cell C18 to find the minimum value:
=SUBTOTAL(5,D5:D14)
 Here, the argument 5 corresponds to the minimum value.
Step 5: Press Enter
 Press Enter to calculate the minimum value.
Result: By using the SUBTOTAL function, we obtain both the maximum and minimum values.
Additional Operations: Apart from SUM, the SUBTOTAL function allows us to perform other operations such as PRODUCT, Standard Deviation, and Variance. In total, there are 11 operations available through SUBTOTAL.
Things to Remember
When dealing with hidden or filtered objects, use arguments 101111 in the first argument of the SUBTOTAL function.
Download Practice Workbook
You can download the practice workbook from here:
<< Go Back to Excel Functions  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!