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 1-11 or 101-111.
|
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 1-11 and 101-111 modes of the Excel SUBTOTAL function ignore the rows that have been filtered-out. 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 filtered-out 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 1-11 or 101-111 you will get a #VALUE! error.
- If any of the ref arguments contains a 3-D reference you will get a #VALUE! error.