Respect a Filter with Sumproduct

Quite often Sumproduct is used in cases where a Pivot table just doesn’t cut it. Here’s how to make Sumproduct respect filters in a data table.

Page Content hide
1 Pick a column in your data table
2 Use Subtotal
3 Filter the table
4 How does that work?
5 Break it down
6 Summary

 

Pick a column in your data table

 

Lets use the ID column, and make a name range – In this case I am using a data table, but you could equally use a dynamic range.

limiter = dataTable[ID]

Use Subtotal

We are going to leverage the subtotal function , since it respects the rows filtered out in a table, and we can intercept that to create a series of 1/0 to indicate whether the row is filtered or not. To make this very clean , we can create another named range
 

respectFilter =(SUBTOTAL(102, OFFSET(limiter,ROW(limiter)-MIN(ROW(limiter)),,1,1)))

That gives us this result

Filter the table

You can see that the formula respects any filtering done on the table. 
 

How does that work?

Evaluating the formula shows that the Subtotal() function associated with the respectFilter named range  returns a series of 1/0 depending on whether the row is filtered out. It is this behavior that eliminates the hidden rows from the sum.
 

Break it down

=sumproduct(SUBTOTAL(102, OFFSET(limiter,ROW(limiter)-MIN(ROW(limiter)),,1,1))))

  • 102 instructs subtotal to do a COUNTA() function – so when the rows are counted one at a time, the result is either 1 or 0. 
  • Offset (,,1,1) means to look at the range 1 at a time
  • row(limiter)-min(row(limiter)) calculates the current cell by subtracting the first row of the limiter range from the current row
  • An array of size rows(limiter) is created with the result of a series of counta() single cells, which equate to 0/1
  • This array is intercepted by sumproduct and applied to its other arguments

Summary

This is a neat way to make Sumproduct respect filters applied to its target. Putting the whole thing in a named range like sumproduct(respectFilter) keeps your worksheet clear of clutter. For more tips like this see Get Started Snippets. 

In the meantime why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.

In the meantime why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.