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