The Excel SUBTOTAL function returns the subtotal of the specified values. The function works like a combination of aggregation formulas of Excel, and allows you to determine the function with the use of a separate parameter. What separates the SUBTOTAL function from the other similar formulas is its ability to exclude hidden cells and other SUBTOTAL functions. In this guide, we’re going to show you how to use the Excel SUBTOTAL function and also go over some tips and error handling methods.
Supported versions
 All Excel versions
Syntax
Arguments
function_num 
The code which sets which function is to be used for calculating the subtotal. You can choose a number between 111 or 101111.

ref1 
The first reference for which you want the subtotal 
ref2 
Optional. Additional references. You can add up to 254 arguments. 
Function_num
Function_num 
Function_num 
Function 
(includes hidden values) 
(ignores hidden values) 

1 
101 
AVERAGE 
2 
102 
COUNT 
3 
103 
COUNTA 
4 
104 
MAX 
5 
105 
MIN 
6 
106 
PRODUCT 
7 
107 
STDEV 
8 
108 
STDEVP 
9 
109 
SUM 
10 
110 
VAR 
11 
111 
VARP 
Examples
Simple scenario
Hidden Rows
You can use the SUBTOTAL to aggregate only visible cells. To do this, you must set a number between 101 and 111 for the argument function_num. For example,
Note the difference between two use cases below. The value in the cell E20 is greater than that in cell F20. Although both formulas perform a sum (9, 109), the values are different because of the hidden rows. You can see the rows 4 and 10 are hidden.
Filtered Values
Both 111 and 101111 modes of the Excel SUBTOTAL function ignore the rows that have been filteredout. This means that you do not need another formula to find the subtotal of a specific category, and instead you can use Excel’s filter feature. Notice how the two functions calculate the totals below.
Tips
Feats Summary
 The Excel SUBTOTAL function ignores other SUBTOTAL functions in the specified range.
 Use function numbers between 101 and 111 to ignore hidden rows.
 The SUBTOTAL function doesn’t calculate the values from filteredout rows. This can work perfectly with Excel Tables.
Alternative ways to add SUBTOTAL function
 You can easily add SUBTOTAL functions to your tables by clicking the Subtotal icon under the Data tab in the Ribbon.
 Excel Tables allow adding a total row automatically. The “Totals” row created this way uses the SUBTOTAL formula.
Issues
#VALUE!
 If the function_num argument is not an integer between 111 or 101111 you will get a #VALUE! error.
 If any of the ref arguments contains a 3D reference you will get a #VALUE! error.