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:

SUBTOTAL Function Examples

Demo data:

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.

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.