Quite often Sumproduct is used in cases where a Pivot table just doesnt cut it. Here's how to make Sumproduct respect filters in a data table. Pick a column in your datatableLets use the ID column, and make a name range - In this case Im using a data table, but you could equally use a dynamic range. limiter = dataTable[ID] Use SubtotalWe 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 TableYou 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))))
SummaryThis 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.You want to learn Google Apps Script?Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'Reilly, Amazon and all good bookshops. You can also read a preview on O'Reilly If you prefer Video style learning I also have two courses available. also published by O'Reilly. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Quirky Functions >