Did you know that you can use the SUBTOTAL Function to SUM filtered data in Excel?
We all know filtering of data using either by inserting tables or by using filter feature in Sort and filter group.
For general adding operation, we use SUM Function. But when we filter the data the total displayed is of all the items in the data range. It doesn’t display the total of filtered data.
Excel has another function for this purpose. You can use the SUBTOTAL function to carry on this operation.
Let us understand it with an example.
We have a list of about 50 customers, their invoice numbers and their outstanding invoice amounts. We have already inserted tables for easy filtering of data.
Using SUM Function in Filtered Data
First of all, let us insert the SUM function in the last cell of Amounts.
Formula: =SUM(C2:C50)
Let’s filter the table for a particular customer say “Abhishek Cables”. While filtering you must keep in mind that checkbox nex to the required name is only checked.
You can see there is no change in the totals. This is because we have used the SUM Function.
Thus, using SUM Function isn’t solving our problem.
SUM Filtered Data Using SUBTOTAL Function
The solution to our problem lies in using the SUBTOTAL Function.
Change the formula from =SUM(C2:C50) to =SUBTOTAL(9,C2:C50) and see the magic.
In filtered list, SUBTOTAL always ignores values in hidden rows regardless of the function argument.
Now you can see that every time we choose a different name the total changes accordingly.
Click on the link below to download the excel file with the above sample data:
Apart from SUM, you can use other operations like AVERAGE, COUNT, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR and VARP with SUBTOTAL Function. You only need to change the respective function number argument.
Click here to learn how to use the SUBTOTAL Function.
To learn other financial function like CUMPRINC and ACCRINTM Functions, please click on the name of the function.
Additionally, you can download also accounting templates like Startup Costs Calculator, Salary Sheet and Sales Commission Calculator from here.
We thank our readers for liking, sharing and following us on different social media platforms.
If you have any queries please share in the comment section below. I will be more than happy to assist you.
