**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.

## 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: 3 Suitable Examples

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

**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.

**Step 3:**

- Get the
**Subtotal**dialog box. - Select the column from the dropdown.

**Step 4:**

- Select the required function from the
**Use function**.

**Step 5:**

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

**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**.

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**.

**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**.

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

## How to Use SUBTOTAL Function in Excel: 3 Examples

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)`

**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.

**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**.

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

### 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.

**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.

**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.

**Download Practice Workbook**

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

## Conclusion

In this article, we described how to use the Excel SUBTOTAL function. I hope this will satisfy your needs. Please give your suggestions and feedback in the comment box.

**<< Go Back to Excel Functions | Learn Excel**