An Overview of SUBTOTAL Function  Microsoft Excel
The SUBTOTAL function is one of the math and trigonometry functions in Microsoft Excel. It can be used as a standalone function as well as in collaboration with the other functions. This article will give you an overview of the SUBTOTAL function in Excel.
Objectives
The SUBTOTAL function can calculate summation, average, count, maximum, minimum, multiplication, etc. within a specified subrange. Its characteristics are controlled by its first argument named function_num. The value of the function_num can be either 111 or 101111.
Syntax
=SUBTOTAL(function_num,ref1,[ref2],...)
Arguments
 function_num: It accepts a number within 111 or 101111. The number specifies one of the 11 subfunctions. To include the manually hidden rows, you have to insert a number from 111. To exclude the hidden rows, you have to insert a number from 101111. One important thing is this function always ignores the filteredout rows.
 ref1: The first subrange within which you want to calculate the subtotal.
 ref2: Noncompulsory. The second subrange is within which you want to calculate the subtotal. The SUBTOTAL function supports up to 255 subranges to calculate the subtotal.
Function Numbers
SubFunctions  Include Hidden Rows  Exclude Hidden Rows 
AVERAGE  1  101 
COUNT  2  102 
COUNTA  3  103 
MAX  4  104 
MIN  5  105 
PRODUCT  6  106 
STDEV  7  107 
STDEVP  8  108 
SUM  9  109 
VAR  10  110 
VARP  11  111 
Output
A numerical value.
Examples of SUBTOTAL Function
Suppose, you have a series of numbers in the range A2:A7. You also have another series of numbers in the range A8:A12. Now you want to count them all. In this particular case, you can use the SUBTOTAL function to count all the items from two distinct ranges. Just use the following formula.
=SUBTOTAL(2,A2:A7,A8:A12)
This formula will count all the numbers in two distinct ranges.
Now here are more examples of the SUBTOTAL function. Take a look.
Usage Guide of SUBTOTAL Function
Step_1: Start with inserting an equal sign (=) in a blank cell.
Step_2: Then type SUBTOTAL.
Step_3: Type open parenthesis “(“.
As you type the open parenthesis, Excel shows the syntax of the SUBTOTAL function.
You will also see the list of all the subfunctions of the SUBTOTAL function.
Step_4: Insert a number to refer to one of the subfunctions.
For this instance, I’m inserting 2 to refer to the COUNT function.
Step_5: Then insert a comma (,).
After inserting the comma, Excel will ask you for the second argument which is ref1.
Step_6: Now insert the first range to count.
Here I’m inserting the range A2:A4.
Step_7: Then insert a comma (,) again.
Step_8: Now insert the second range as ref2 to count.
Here I’m inserting the range B2:B4.
In this way, you can insert a total of 255 distinct ranges.
Step_9: Finally, add a closing parenthesis “)” to complete the syntax.
Step_10: Now, press ENTER.
The SUBTOTAL function will return a numerical value (6) which is the count number of the nonblank cells of the given two ranges.
Availability














Remarks
 The SUBTOTAL function returns a #VALUE! Error, you if insert a function number other than 111 or 101111.
 It includes all the manually hidden rows for the function_num 111.
 It ignores all the manually hidden rows for the function_num 101111.
 The SUBTOTAL function always ignores the filtered out rows, no matter what the function_num
 For any 3D references, the SUBTOTAL function returns a #VALUE!
 This function ignores all the nested SUBTOTAL
Conclusion
The SUBTOTAL function is one of the math and trigonometry functions in Excel. If you have any questions regarding the SUBTOTAL function, please comment below. Thanks!