Function: SUBTOTAL

by | Mar 14, 2019 | Excel Formulas

excel subtotal function

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)
instead of =SUBTOTAL(9,$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.

 Download Workbook

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.