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.

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Kasper Langmann, Co-founder of Spreadsheeto

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.

Kasper Langmann, Co-founder of Spreadsheeto

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.

subtotal-function-options

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

This will make more sense in the examples.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

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.

subtotal-data

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.

Kasper Langmann, Co-founder of Spreadsheeto
subtotals

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.

filtered-sales

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.

filtered-skus

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.

Example 2: excluding hidden values

It is important to note the difference between filtered data and hidden data.

While we have seen some examples of filtering data, this is not the same thing as hiding data.

For example, we may want to hide all the rows in our data that have SKU’s beginning with “DETA.”

To do this, we can highlight those rows, right click, and select Hide.

hide-rows

Similar to filtering, this removes those rows from visibility.

visible-rows

But, we cannot use the same function_num arguments as we did in our previous examples.

Kasper Langmann, Co-founder of Spreadsheeto

We need to use their counterparts that will exclude hidden values.

Notice that before we hide our rows, our SUBTOTAL formulas output the same thing.

This is regardless of which variation of the function_num argument we use.

include-hidden

However, once we hide the rows with “DETA” in the SKU, only those formulas with 109 and 101 change.

exclude-hidden

The formulas with 9 and 1 as the ‘function_num’ remain unchanged.

This is the difference between using SUBTOTAL function with filtered versus hidden data.

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.