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.
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]
respectFilter =(SUBTOTAL(102, OFFSET(limiter,ROW(limiter)-MIN(ROW(limiter)),,1,1)))
That gives us this result
=sumproduct(SUBTOTAL(102, OFFSET(limiter,ROW(limiter)-MIN(ROW(limiter)),,1,1))))
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.
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines