Using the SUBTOTAL Excel Function: Real World Examples

Last Modified: June 3, 2024 - 7 min read

Hannah Recker
subtotal excel function

Envision you have an enormous set of data in Excel, filled with rows of valuable data. Now you need to perform calculations such as sums, averages and counts but only for precise fragments for your data. As you filter and sort your data, wouldn’t it be marvelous if your calculations optimized automatically to reflect only the noticeable rows? This is where the significant SUBTOTAL function in Excel comes to your ransom.

The SUBTOTAL Function is an adaptable and dynamic tool that permits to perform a variety of calculations on refined or visible data subsets. Unlike standard functions that operate on all rows, SUBTOTAL adjusts to your data view, ensuring your outcomes are always pertinent and up-to-date.

Whether you are handling sales figures, dissecting performance scores, or operating with any large set of data, SUBTOTAL helps you get precise perceptions without the difficulty of continuously optimizing formulas.

In this article, we will discover how to harness the power of the SUBTOTAL function, concealing practical instances and synopsis that substantiates its adaptability and effectiveness.

Performing operations like SUM, AVERAGE, COUNT, MIN, MAX, etc., on a range of cells, the SUBTOTAL Excel function uses specified arguments.

Why Use SUBTOTAL Instead Of SUM?

The benefit of using the SUBTOTAL Excel function (over SUM or any function it performs based on the argument) is that it ignores hidden rows or rows excluded by filters from the calculation. It also ignores cells containing other SUBTOTAL functions when performing any calculation.

So let’s see how exactly the SUBTOTAL function works.

How The SUBTOTAL Excel Function Works

Let’s start with the syntax.

The SUBTOTAL Excel function is defined as: =SUBTOTAL(function_num, ref1, [ref2],…)

For the function_num argument, you’ll need to choose a number for the function you want performed. These numbers are:

function_numFunctionFeature
1AVERAGEIgnores only filtered cells
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV.S
8STDEV.P
9SUM
10VAR.S
11VAR.P
101AVERAGEIgnores both filtered and hidden cells
102COUNT
103COUNTA
104MAX
105MIN
106PRODUCT
107STDEV.S
108STDEV.P
109SUM
110VAR.S
111VAR.P

As soon as you start typing the SUBTOTAL function syntax, you’ll see all these options in a drop-down menu.

subtotal function syntax

Based on the function_num argument you choose, you can perform the 11 functions listed above. You can also choose the argument to ignore only filtered cells or filtered and hidden cells.

The ref_1 argument is the cell range on which you want to perform the function.

The ref_2, ref_3 ref_255 are optional arguments that contain additional ranges to include in calculating the SUBTOTAL.

Now that you understand how to write a SUBTOTAL Excel function, let’s dive into some examples of how to use it to perform the desired function on a data set.

Examples Of The SUBTOTAL Excel Function

Here are a variety of use-cases for the SUBTOTAL Excel function:

Example #1: Difference Between function_num 1-11 And function_num 101-111

We’ll demonstrate this difference using function_num 9 and 109, both of which will calculate the sum of the given range.

how to write subtotal formula

Cell F4 is calculating the SUM using the function_num argument 9, whereas cell F5 is calculating the SUM using the function_num argument 109.

Let’s manually hide rows 12 and 13 and see how the SUM calculation changes:

hide rows when sum calculation changes

Cell F4 ignores the hidden rows, whereas cell F5 doesn’t.

Now, we’ll filter the table to show all data except the “East” region. Let’s see what impact that has on the two subtotals:

filter subtotal by conditions

Both the subtotals have ignored the data that has been hidden by the applied filter.

Example #2: Calculating SUBTOTAL Values Of Subsets Within The Main Data Set

If you had to calculate the average/sum/min/max of each region temperature (in the example above), you can use the subtotal Excel function like this:

calculate average of temperatures with subtotal function

Now, let’s say you want to calculate the average of all the regions’ temperatures, you can use the SUBTOTAL function across the entire range of temperatures. Like so:

subtotal across range of temperatures

This will return the value 64.91667, which is the average temperature across all regions.

You’ll notice that even though we’ve included each region’s average temperature in the ref1 argument, the SUBTOTAL function in cell E4 ignores those values as they contain separate SUBTOTAL functions.

Example #3: Performing Operations On Data In A Table

The SUBTOTAL Excel function is quite useful when performing operations on data in a table as it ignores cells hidden by a filter.

Let’s say you have a list of products and the respective units sold, and you want to find the highest-selling product for a particular specification. To do so, you’d need to follow these steps:

You’ll identify the maximum value in the “Units Sold” column. But instead of using the MAX function, you’d use the MAX function within SUBTOTAL.

subtotal with max function

The formula =SUBTOTAL(4,C4:C12) returns the value 522.

Coefficient Excel Google Sheets Connectors
Try the Free Spreadsheet Extension Over 500,000 Pros Are Raving About

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

Get Started

Next, you’ll use the INDEX-MATCH function to identify which product this value corresponds to.

index match in subtotal

The formula =INDEX(A4:A12,MATCH(C13,C4:C12,0)) returns the value “Oven Y,” which corresponds to 522 units sold.

Now, if you want to identify which is the highest-selling product in “Silver” color, all you have to do is filter the table to show only “Silver” colored products.

conditional formatting on subtotals
You don’t have to even touch any formula to get the data you want, as the SUBTOTAL and INDEX-MATCH functions are already defined in a way to do that for you.
index match to define the results you want
You’ll get the result “Oven Z” in cell C14 as it’s the highest selling “Silver” colored product.

Example #4: Calculating the Average Using SUBTOTAL Function in Excel

Scenario: You have a set of data of employee scores for a department. The set of data indulges the performance score for each employee in a year. You want to calculate the standard performance score while permitting for the adaptability to filter the data and have the standard update spontaneously based on visible rows.

average performance score in excel

Step-By-Step Instructions:

Launch Excel

Open the excel sheet and enter the data in two rows as shown above.

Enter the SUBTOTAL Formula:

Choose the cell where you want to exhibit the average performance score, for instance cell ‘B12’.

Type the Formula ‘=SUBTOTAL(101, B2:B11).’

average performance score formula placement with subtotal

As you can see, the average performance score is 85.

Using the ‘SUBTOTAL’ Function with function number ‘101’ permits you to dynamically calculate the average based on the refined data, giving more adaptability in data analysis.

Dealing With Common Errors When Using The SUBTOTAL Excel Function

When using the SUBTOTAL Excel function, you may run into some errors, which can be frustrating. Listed below are the common errors and how to resolve them:

Error TypeSolution
#NAME?You’ve probably spelled the function name (SUBTOTAL) wrong. Correct the spelling to get rid of this error.
#VALUE!You’ve entered a value for function_num that is a number not between 1-11 or 101-111. Select the proper value to get rid of this error.
#DIV/0!You’ll encounter this error when trying to do a mathematical operation on a cell/s that doesn’t contain numerical data. So double check your ref argument.

Now that you’re equipped with knowledge about the SUBTOTAL Excel function, why not take your skills further? Explore how Coefficient can transform your spreadsheet tasks into seamless experiences.

Using The SUBTOTAL Excel Function

This function is an imperative tool for anyone working with huge and dynamic sets of data. Its capability to adjust calculations based on noticeable data, whether refined or sorted, provides a substantial benefit over average functions. With SUBTOTAL, you can smoothly perform numerous operations such as sums, averages and counts, ensuring that your perceptions are always pertinent and precise.

The SUBTOTAL Excel function can help you perform 11 different functions on range, and ignore filtered and/or hidden cells from those calculations.

However, you may not have seen the SUBTOTAL Excel function being used a lot as Pivot Tables offer similar functionality while being more versatile.

Whichever function you choose to use, it will require a good amount of manual data input and repetitive actions. But it doesn’t have to be this way.

With Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide, you can connect to any data source, import live data, and automate spreadsheet workflows. Get started today.

Connect Live Data to Excel Instantly

Automatically sync data from any source into Excel and keep it on a refresh schedule with Coefficient.

Try the Spreadsheet Automation Tool Over 500,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Hannah Recker Growth Marketer
Hannah Recker was a data-driven growth marketer before partying in the data became a thing. In her 12 years experience, she's become fascinated with the way data enablement amongst teams can truly make or break a business. This fascination drove her to taking a deep dive into the data industry over the past 4 years in her work at StreamSets and Coefficient.
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies