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

This article illustrates how to use the SUBTOTAL function in Excel with filters. It is necessary when you need to apply some functions to a filtered dataset. Because using other functions will ignore the filter operation and always return the same result. The following picture highlights the purpose of this article. Have a quick look through it to learn how to do that.

How to Use SUBTOTAL in Excel with Filters

Excel SUBTOTAL Function: Overview


The SUBTOTAL function in Excel will allow you to apply some particular functions to the visible filtered cells. It can return results only relevant to the filtered cells.


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


function_num: Required. Refers to a particular function. Used for output as like as the output returned by that particular function.

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

[ref2]: These arguments are optional. It allows you to enter multiple ranges in the formula.

First, assume you have the following dataset. It contains the sales amount for some products. Now, you want to filter the dataset based on different criteria and get the respective total sales amount.

Step 1: Applying Excel SUBTOTAL Function in Dataset

  • In the first step, enter the following formula in cell F12 to get the total sales amount. Here, 9 in the formula refers to the SUM function in Excel. You can change it if you want to refer to other functions based on the list given earlier.

Use SUBTOTAL in Excel with Filters

Read More: How to Insert Subtotals in Excel

Step 2: Applying Excel Filter Command

  • Next, select anywhere in the dataset. After that, press CTRL+SHIFT+L to apply Filter to the dataset. Now, you will see the filter buttons at the lower-right corners of the header cells.

Step 3: Getting Partial Total Based on Category When Filter Data

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

  • After that, you will see the following result.

  • Now, filter the dataset by another category (Shirt). Then, the total will change automatically although the formula bar always shows the same formula.

Use SUBTOTAL Function in Excel with Filters

Error That Occurs When Using SUBTOTAL in Excel with Filter

The SUBTOTAL function cannot ignore errors. It returns an error if there are errors in any of the cells within the referred range in the formula.

You can use the AGGREGATE function instead. Then, you will get the desired result while ignoring the errors at the same time.

Things to Remember

  • You should keep in mind that Excel doesn’t consider hidden values the same as the 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.
  • You have to use different function_num arguments to get the desired results. For example, use 4 to get the maximum sales amount.

Download Practice Workbook

You can download the practice workbook from the download button below.


Now you know how to use the SUBTOTAL function in Excel with filters. Please let us know if this article has helped you with your problem. You can also use the comment section below for further queries or suggestions. Stay with us and keep learning.

Related Articles

<< Go Back To Subtotal in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF