**How to use Excel function SUBTOTAL**

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

In this tutorial, we turn our attention to the SUBTOTAL function in Excel.

You may think of a sum of selected values. But, the SUBTOTAL function is **capable of much more**.

Also to its ability to return a sum, it can also give the user an average, count, max, and several other results.

We will look at a few examples in this post to show **what SUBTOTAL does and how to use it**.

**Syntax**

It can be challenging to explain the full extent of what the SUBTOTAL function is capable of. We will start by examining the syntax.

This is because the first argument represents a particular function itself.

**The syntax of the SUBTOTAL function**

**=SUBTOTAL(function_num, ref1, [ref2],…)**

Everything boils down to the **function_num argument** and the **ref arguments**.

The **function_num** and **at least one ref** are required.

The ref1 (…ref2, ref3, etc.) argument is the reference to the cell or range of cells **we want to apply SUBTOTAL to**.

Now let’s talk about that function_num argument. This is any of 22 available functions denoted by a code number.

The codes available range from 1 to 11, or 101 to 111. If you attempt to put any other value in this argument, it will cause the #VALUE! error.

Before we move on, it is necessary to understand something else.

The SUBTOTAL function **always ignores values removed by auto-filter**.

What we are about to see is that there are two sets of the same function_num available.

But, one set includes hidden cell values while the other set excludes hidden cell values. More on that in a bit.

Notice that there are 11 functions available. But, **each has a duplicate that excludes hidden values**.

This will make more sense in the examples.

**Example 1: using SUBTOTAL with filtered data**

One of the useful ways to apply the SUBTOTAL function is **when filtering data**.

Let’s say you have sales data for an online business you run and you would like to get a quick look at a few metrics.

You want totals, averages, and maximums based on a single item or even a particular sales channel.

It’s simple enough to get the total for all sales in a table like this by using the SUM function.

But, let’s put the ‘SUBTOTAL’ function to work so we can see a few other figures.

We will find the sales totals, maximum sale, and average sale when we filter the data down.

Note that each of the SUBTOTAL formulas in this example use the range $E$3:$E$29 for the ref1 argument.

This is the entire column of values for sales.

For Sub Total our function_num argument is 9. For Maximum Sale and Average Sale, our function_num arguments are 4 and 1.

The Grand Total is the result of a formula using the ‘SUM’ function. We will use that to compare and contrast to the results we get with our ‘Sub Total’ formula as we begin to filter our table.

To see how our SUBTOTAL formulas behave, we can first **filter the Channel column on Online sales only**.

Note the Grand Total is still the **actual grand total of sales**. It include rows that are no longer visible because of the filter.

But, the fields with the SUBTOTAL formulas have adjusted only online sales.

We can continue to drill down into the data by filtering the SKU column.

The SUBTOTAL formulas **continue to recalculate** based on values not filtered out. This leads us to our next set of examples.

We will look at a scenario in which the SUBTOTAL function does not ignore values.

It excludes them.

**Conclusion**

This was a short introduction to SUBTOTAL. We only demonstrated a few of the available functions.

But it gives you some exposure to the variations based on filtering your data as opposed to hiding it.

SUBTOTAL gives you different options that accommodate **different ways to analyze your data**.