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

Get FREE Advanced Excel Exercises with Solutions!

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

Objective:

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.

Syntax:

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

Arguments:

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.
=SUBTOTAL(9,F5:F10)

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.


Conclusion

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

 

 

ExcelDemy
Logo