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

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