How to Use SUBTOTAL in Excel with Filters (With Quick Steps)

This article will demonstrate how to use the SUBTOTAL function in Excel with filters.

Excel SUBTOTAL Function: Overview

Objective:

The SUBTOTAL function allows us to apply a selection of functions to the visible filtered cells. It returns results only relevant to the filtered cells.

Syntax:

Arguments:

function_num: Required. Refers to a function from the list below. The output of the SUBTOTAL function will be that of the selected function here.

ref1: Required. Returns an output based on this range.

[ref2]: Optional arguments that allow entering multiple ranges in the formula.

Suppose we have the following dataset containing the sales details for some products.

Let’s filter the dataset based on different criteria, and find the corresponding total sales amounts.

Step 1 – Applying the SUBTOTAL Function

• Enter the following formula in cell F12 to get the total sales amount:

Here, 9 in the formula refers to the SUM function. To use a different function with SUBTOTAL, enter the corresponding number from the list provided above.

Read More: How to Insert Subtotals in Excel

Step 2 – Applying the Filter Command

• Select anywhere in the dataset.
• Press CTRL+SHIFT+L to apply Filter to the dataset.

The filter buttons will be visible at the lower-right corners of the header cells.

Step 3 – Finding a Partial Total Filtered by Category

• Filter the dataset based on a particular category (Pant) using the filter button, as shown in the following picture.

The result is as follows.

• Now filter the dataset by another category (Shirt).

The total will change automatically although the formula bar keeps showing the same formula.

Errors When Using SUBTOTAL with the Filter

It is not possible to ignore errors with the SUBTOTAL function. It will return an error if there are errors in any of the cells within the referred range/s in the formula.

In these cases, we can use the AGGREGATE function instead, which has similar functionality to the SUBTOTAL function, but also the ability to ignore errors.

Things to Remember

• Excel doesn’t consider hidden values the same way as filtered-out values. So, you should use the proper function_num argument if there are hidden values in your dataset.
• The 101 series of numbers for the function_num argument also excludes the header values.
• Use different function_num arguments to get the desired results. For example, use 4 (the MAX function) to get the maximum sales amount.

Download Practice Workbook

Related Articles

• How to Sort Subtotals in Excel
• How to Make Subtotal and Grand Total in Excel
• VBA Code for Subtotal in Excel
• How to Remove Subtotals in Excel

<< Go Back To Subtotal in Excel | Learn Excel