How to Use the SUBTOTAL Function in Excel (3 Methods)

Introduction to the SUBTOTAL Function

The SUBTOTAL function returns a list or database and falls under the Math/Trig function category. It offers 11 Excel functions that can be executed by specifying 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 the SUBTOTAL will use. Numbers 1-11 includes manually hidden rows, while 101-111 exclude them. Filtered-out cells are always excluded.
ref1 Required The first named range to be used in the function.
ref2 Optional You can use 2 to 254 ranges or references.

Return

The SUBTOTAL 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 the SUBTOTAL Function

The SUBTOTAL function can perform 11 different Excel operations or functions. These functions are specified using the function_num argument. It also offers two conditions for each function: including hidden and filtered data or ignoring the hidden data. Here are the available functions along with their corresponding numbers:

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

Let’s demonstrate how to use the SUBTOTAL function with a data set from two clothing store branches.

Using the SUBTOTAL

  • Sort the Data:
    • Sort the data in Column B.

  • Access the Data Tab:
    • Go to the Data tab.
  • Select Subtotal:
    • Choose Subtotal from the Outline command.

Use of SUBTOTAL Function in Excel

  • Configure the Subtotal Dialog Box:
    • Select the desired column from the dropdown.

Use of SUBTOTAL Function in Excel

    • Choose the function you want to apply.

Use of SUBTOTAL Function in Excel

    • Specify the columns where the function should perform.

Use of SUBTOTAL Function in Excel

  • Press OK:
    • Confirm your settings.

By following these steps, you can apply the sum operation based on the product category. You’ll obtain subtotals for each product and a grand total for all products.

  • Clicking on 1 in the upper left corner provides only the Grand Total.

  • Selecting 2 includes the total of each product separately.

Use of SUBTOTAL Function in Excel

  • Finally, choosing 3 displays each product with individual details and totals.

This method is useful when you need a detailed subtotal operation. Alternatively, you can use formulas as per your specific requirements.


Applying the SUBTOTAL by Inserting Formula

  • Navigate to Cell C17:
    • Go to Cell C17.
  • Enter the SUBTOTAL Function:
    • In Cell C17, enter 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

  • Select the Data or Range:
    • Select the data or range to which you want to apply the function.
    • The applied formula in our example is:

=SUBTOTAL(9,D5:D14)

  • Press Enter:
    • Press Enter to execute the sum operation using the SUBTOTAL function.


Method 1 – Calculate the Average Using Excel’s SUBTOTAL Function

To find the average, follow these steps:

  • Modify the Data Set:
    • Modify the data set to perform the average operation.

  • Enter the SUBTOTAL Function:
    • In Cell C17, enter the SUBTOTAL function.
    • Use 1 as the first argument to indicate the average.
    • The formula becomes:
=SUBTOTAL(1,D5:D14)

Calculate the Average Using Excel SUBTOTAL Function

  • Press Enter:
    • Press Enter to obtain the average price using the SUBTOTAL function.


Method 2 – Count Operation in Excel Using SUBTOTAL

In this section, we’ll demonstrate how to perform the count operation using the SUBTOTAL function in Excel. The SUBTOTAL function allows us to display both the COUNT and COUNTA operations. Specifically:

  • The COUNT function counts only numeric values.
  • The COUNTA function counts both numeric and alphabetic values.

Step 1: Data Modification

  • Before applying the operation, make any necessary modifications to the data.

Step 2: Applying Formulas

  • Apply the following formulas individually to cells C17 and C18:

Count Operation in Excel by SUBTOTAL

    • To perform the COUNT operation:
=SUBTOTAL(2,D5:D14)
    • To perform the COUNTA operation:
=SUBTOTAL(3,D5:D14)
    • Press Enter after entering each formula.

Count Operation in Excel by SUBTOTAL

Results: The formula with argument 2 indicates the COUNT operation, while the formula with argument 3 indicates the COUNTA operation.


Method 3 – Using SUBTOTAL to Find Maximum and Minimum Values

In this section, we’ll use the SUBTOTAL function to determine the maximum and minimum values.

Step 1: Separate Boxes for Maximum and Minimum Values

  • Create two separate cells to display the maximum and minimum values.

Step 2: Maximum Value Formula

  • Enter the following formula in cell C17 to find the maximum value:
=SUBTOTAL(4,D5:D14)
  • Here, the argument 4 corresponds to the maximum value.

Insert SUBTOTAL Function to Find Max/Min Value

Step 3: Press Enter

  • Press Enter to calculate the maximum value.

Step 4: Minimum Value Formula

  • Enter the following formula in cell C18 to find the minimum value:
=SUBTOTAL(5,D5:D14)
  • Here, the argument 5 corresponds to the minimum value.

Insert SUBTOTAL Function to Find Max/Min Value

Step 5: Press Enter

  • Press Enter to calculate the minimum value.

Result: By using the SUBTOTAL function, we obtain both the maximum and minimum values.

Additional Operations: Apart from SUM, the SUBTOTAL function allows us to perform other operations such as PRODUCT, Standard Deviation, and Variance. In total, there are 11 operations available through SUBTOTAL.


Things to Remember

When dealing with hidden or filtered objects, use arguments 101-111 in the first argument of the SUBTOTAL function.


Download Practice Workbook

You can download the practice workbook from here:


<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo