How to Use SUBTOTAL Function in Excel (3 Suitable Examples)

SUBTOTAL is one of the default functions of Microsoft Excel. This function provides the opportunity to perform a group of Excel function operations. We can apply different Excel functions by inserting this function. In this article, we will discuss how to use this Excel SUBTOTAL function with examples and proper illustrations.

This is an overview of the SUBTOTAL function. In the below sections we will describe how to use this function in detail.


Download Practice Workbook

Download the following practice workbook to exercise while you are reading this article.


Introduction to SUBTOTAL Function

The SUBTOTAL function returns a list or database. It’s in Math/Trig function category. It offers 11 Excel functions to perform by inserting the corresponding argument.

Syntax:

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

Argument Required/Optional Explanation
function_num Required This number ranges from 1 to 11 or 101 to 111 specifies which function will be used for the SUBTOTAL. 1-11 includes manually hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.
ref1 Required This is the first named range, which will be used in the function.
ref2 Optional We can use 2 to 254 ranges or references.

Return:

This function returns any numeric value.

Available in:

Microsoft Excel 365, Microsoft Excel 365 for Mac, Excel for the web Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2019 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel for Mac 2011, Excel Starter 2010.


Calculations Available with SUBTOTAL Function

The SUBTOTAL function can perform 11 different Excel operations or functions. These functions are specified in the function_num argument. It also offers two conditions for every function. One is including hidden and filtered data and the other is ignoring the hidden data. The available functions with their corresponding numbers are given below:

Function With Hidden Data Without Hidden Data
AVERAGE 1 101
COUNT 2 102
COUNTA 3 103
MAX 4 104
MIN 5 105
PRODUCT 6 106
STDEV.S 7 107
STDEV.P 8 108
SUM 9 109
VAR.S 10 110
VAR.P 11 111

How to Use SUBTOTAL Function in Excel

Now, we show the use of the SUBTOTAL Function. For this, we consider a data set of clothing stores from two branches.

Dataset for using Excel SUBTOTAL Function

Apply SUBTOTAL from the Data Tab:

Step 1:

  • First, sort the data in Column B.

Step 2:

  • Go to the Data tab.
  • Select Subtotal from the Outline command.

Use of SUBTOTAL Function in Excel

Step 3:

  • Get the Subtotal dialog box.
  • Select the column from the dropdown.

Use of SUBTOTAL Function in Excel

Step 4:

  • Select the required function from the Use function.

Use of SUBTOTAL Function in Excel

Step 5:

  • Now, choose the columns where the function will perform.

Use of SUBTOTAL Function in Excel

Step 6:

  • Then, press OK.

Here, we apply the sum operation based on the product category. We also get some for each product and finally also get the sum for all the products.

Step 7:

  • In the left upper corner, 1,2,3 numbers are shown.
  • First, click on 1.

Here, we get only the Grand Total.

Step 8:

  • Then, select 2.

We get the Grand Total including the total of each product separately.

Step 9:

  • Finally, select 3.

Use of SUBTOTAL Function in Excel

Here, we get each product with details separately with the totals.

This method is applied when we want to perform the SUBTOTAL with a detailed operation. We can also simply use the formulas as per our requirements.

Apply SUBTOTAL by Inserting Formula:

Step 1:

  • Go to Cell C17.
  • Write the SUBTOTAL function.
  • In the first argument, a dropdown will appear.
  • Choose the required function or operation from the dropdown.

Use of SUBTOTAL Function in Excel

Step 2:

  • Then select the data or range to apply the function.
  • Here, our applied formula is:
=SUBTOTAL(9,D5:D14)

Step 3:

  • Now, press Enter.

Use of SUBTOTAL Function in Excel

Here, we performed the sum operation using the SUBTOTAL function.

Read More: 51 Mostly Used Math and Trig Functions in Excel


3 Examples with Excel SUBTOTAL Function

Here, we will see different uses of the Excel SUBTOTAL Function.

1. Calculate the Average Using Excel SUBTOTAL Function

We will find the average inserting the SUBTOTAL function in this method.

Step 1:

  • First, modify the data set to perform the average operation.

Step 2:

  • Write the SUBTOTAL Function on Cell C17.
  • We put 1 on the first argument, which indicates average.
  • So, the formula becomes:
=SUBTOTAL(1,D5:D14)

Calculate the Average Using Excel SUBTOTAL Function

Step 3:

  • Then press Enter.

Here, we get the average price by applying the SUBTOTAL function.


2. Count Operation in Excel by SUBTOTAL

In this section, we will show the count operation by the SUBTOTAL function. We can show the COUNT and COUNTA operations of Excel through this SUBTOTAL default function. The COUNT function counts only the numeric values. But COUNTA counts both numeric and alphabetic values.

Step 1:

  • We modify the data before applying the operation.

Step 2:

  • We apply the formulas in Cells C17 and C18 individually.

Count Operation in Excel by SUBTOTAL

Step 3:

  • Now, put the formula to perform the COUNT operation in Cell C17.
=SUBTOTAL(2,D5:D14)
  • Write the formula to perform the COUNTA operation in Cell C18.
=SUBTOTAL(3,D5:D14)
  • Then press Enter.

Count Operation in Excel by SUBTOTAL

We can see the results here. In the formula 2 indicates COUNT and 3 indicates COUNTA operation.


Similar Readings


3. Insert SUBTOTAL Function to Find Max/Min Value

In this section, we will apply the SUBTOTAL function to find out the maximum or minimum value.

Step 1:

  • We mention two separate boxes to show the maximum and minimum values.

Step 2:

  • Now, put the formula on Cell C17. The formula is:
=SUBTOTAL(4,D5:D14)
  • Here, 4 is indicating the maximum value.

Insert SUBTOTAL Function to Find Max/Min Value

Step 3:

  • Now, press Enter.

Step 4:

  • Again, write a formula on Cell C18 to get the minimum value. The formula is:
=SUBTOTAL(5,D5:D14)
  • Here, 5 is indicating the minimum value.

Insert SUBTOTAL Function to Find Max/Min Value

Step 5:

  • Then press Enter.

Here, we get both the maximum and minimum values by using the SUBTOTAL function.

The SUM operation is already shown. We can also perform PRODUCT, Standard Deviation, and Variance operations through this SUBTOTAL function. Including all, we can perform 11 operations through this SUBTOTAL function.


Things to Remember

When we need to avoid the hidden or filtered object, we will use 101-111 in the first argument.


Conclusion

In this article, we described how to use the Excel SUBTOTAL function. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo