Last week, we used the Excel SUBTOTAL function to sum items in a filtered list, while ignoring the hidden rows. Now we’ll look at ways to use Subtotal and SumProduct with filter settings applied.
Here’s a screen shot of the SUBTOTAL function options, that we used last time.
In the comments for that blog post, Sam mentioned that you can also use SUBTOTAL with functions like SUMPRODUCT, to do additional sums or counts, based on the visible data in a filtered table.
Sam sent me his sample Excel file, to show how he uses this technique.
Create Named Ranges
Sam’s workbook has a list with Product, Region and Amount fields. He created dynamic named ranges for the entries in each field, using INDEX and COUNTA.
Note: If you’re using Excel 2010 or Excel 2007, and a named table, you can use table references to create the names.
The SUMPRODUCT Function
With the a simple SUMPRODUCT function, you could sum the amounts for all the North region rows. This works well if the list is not filtered.
However, if the list is filtered, the total for North region still calculates as 545, even though only one amount, 55, is visible.
Use SUBTOTAL with SUMPRODUCT
To solve the problem, Sam created another named range, vAmt, that is based on the Amt range.
The named range uses the SUBTOTAL function to return an array of the values, with zero showing if the row is hidden.
When the vAmt range is used in a SUMPRODUCT formula, it shows the total for only the visible rows.
In the screen shot below, the South region is selected, and the two visible amounts for that region are totaled correctly in cell C2
See the Formula Details
If you select the vAmt in the formula, and press the F9 key, you can see the effect of the SUBTOTAL function. All the amounts show as zero, except for the three visible amounts — 22, 36 and 19.
Download the Sample File
To see Sam’s workbook and formulas, download the SUMPRODUCT SUBTOTAL sample file.
The file is zipped, and it is in Excel 2003 file format.