Md. Shamim Reza
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.
Download Practice Workbook
You can download the practice workbook from the download button below.
Introduction to Excel SUBTOTAL Function
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:
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.
Steps to Use SUBTOTAL Function in Excel with Filters
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: Apply the SUBTOTAL Function to Your Data
- 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)
Read More: How to Make Subtotal and Grand Total in Excel (4 Methods)
Step 2: Apply 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.
Read More: How to Add Subtotals in Excel (4 Easy Methods)
Final Step: Filter Your Data Based on a Category to Get a Partial Total
- 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.
Read More: How to Insert Subtotals in Excel
Error That Occurs in Excel SUBTOTAL 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 exclude 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.
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. Do visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.
