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.