Excel SUBTOTAL Function and Formula with Examples

How to Use the SUBTOTAL Function and Formula in Excel

In this tutorial, you'll learn how to use the SUBTOTAL function and formula in Excel. 

SUBTOTAL() is a function to return a subtotal in a list or database.

SUBTOTAL Syntax

The syntax of the SUBTOTAL() function is as follows:

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

Return value

A subtotal in a list or database

Arguments

  • Function_num: The number that specifies the function to use for the subtotal.
  • Ref1: The first range, or reference for which you want the subtotal.
  • Ref2, ...: The other ranges or references for which you want the subtotal.

Remarks

  • Ref2,... is Optional. You can specify up to 255.
  • Filtered-out cells always excluded.
  • Function numbers 1 - 11 includes the manually-hidden rows.
  • Function numbers 101 - 111 excludes the manually-hidden rows.

List of function numbers used in the SUBTOTAL function

The following is a list of function numbers that you can use in the SUBTOTAL() function:

Function_numFunction_numFunction
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MAX
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

SUBTOTAL Function Examples

Demo data:

 ABCDEF
1Item CodeItem NameQtyUnit Price ($)Amount ($)Date
2Item2001HP Desktop Black151300195006/10/2019
3Item2002HP Desktop Blue241035248406/12/2019
4Item2003Dell Desktop Black251210302506/13/2019
5Item2004Dell Desktop Blue221250275006/13/2019
6Item2005Dell Desktop Green331150379506/14/2019
7Item2006Sony Laptop Green471250587506/14/2019
8Item2007Sony Laptop Black481270609606/15/2019

SUM and SUBTOTAL

In this example, you'll learn the difference between SUBTOTAL() and SUM().

The following example sums all amounts in column E by using the SUBTOTAL() function:

The formula to sum all amounts in column E using the SUBTOTAL function is as follows:

=SUBTOTAL(9,E2:E8)

Here, we've used function number 9, which is the SUM function number.

The above SUBTOTAL() formula returns 259750.

So what is the difference if you use the SUM function?

If you use SUM(), the formula is as follows:

=SUM(E2:E8)

The above SUM formula returns 259750.

Both formulas return the sample result.

Now, let's see the difference between these two formulas.

Let's filter out and exclude the item named Item2001 from the above list.

 ABCDEF
1Item CodeItem NameQtyUnit Price ($)Amount ($)Date
3Item2002HP Desktop Blue241035248406/12/2019
4Item2003Dell Desktop Black251210302506/13/2019
5Item2004Dell Desktop Blue221250275006/13/2019
6Item2005Dell Desktop Green331150379506/14/2019
7Item2006Sony Laptop Green471250587506/14/2019
8Item2007Sony Laptop Black481270609606/15/2019

Now, SUBTOTAL() returns 240250, and SUM() returns 259750.

As you can see, the SUM() formula returns the same, while the SUBTOTAL() returns a different result.

Note that the SUBTOTAL function always excludes the filtered-out cells.

Difference between function numbers 9 and 109

In this example, you'll learn the difference between function numbers 9 and 100 in the SUBTOTAL function.

The following example sums all amounts in column E by using function number 9:

=SUBTOTAL(9,E2:E8)

And the following example sums all amounts in column E by using function 109:

=SUBTOTAL(109,E2:E8)

If you don't filter or manually hide rows, both formulas return 259750.

Now, let's filter and exclude the item named Item2001 from the list.

Still, both formulas return the same results because SUBTOTAL() always excludes the filter-out cells.

Next, let's hide a row that contains the item named Item2001 from the list.

The first formula returns 259750, while the second returns 240250.

Now you have learned the differences between using function numbers 9 and 109 in the SUBTOTAL function.

Note that the SUM function always includes the hidden rows and filter-out cells.

In this tutorial, you've learned how to use the SUBTOTAL function in Excel

SUBTOTAL is a function to return a subtotal in a list or database. 

If you need to include the manually-hidden rows in the calculation, use function numbers 1-11.  

And if you need to exclude the manually-hidden rows from the calculation, use function numbers 101-111 instead.


See also:
Excel ROUNDUP Function with Examples
Excel MIN Function with Examples
Excel COUNTA Function – Count Non-Blank Cells in Excel
Excel COUNTBLANK Function – Count Blank Cells in Excel
Excel SUMIFS Function – Multiple Criteria

Leave a Comment