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.

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

- Choose the function you want to apply.

- Specify the columns where the function should perform.

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.

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.

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)

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:

- To perform the COUNT operation:

=SUBTOTAL(2,D5:D14)

- To perform the COUNTA operation:

=SUBTOTAL(3,D5:D14)

- Press Enter after entering each formula.

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.

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.

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.