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

SUBTOTAL(function_num,ref1,[ref2],...)

# 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. 1-11: Function includes manually-hidden rows 101-111: Function excludes manually-hidden rows 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

=SUBTOTAL(9,\$E\$2:\$E\$17)
formula adds the values from the specified range \$E\$2:\$E\$17 while excluding the SUBTOTAL functions in this range. When the formula is executed, the values in cells E6, E12, and E17 are ignored. ## 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,

=SUBTOTAL(109,\$E\$2:\$F\$17)

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.