limiter = dataTable[ID]
respectFilter =(SUBTOTAL(102, OFFSET(limiter,ROW(limiter)-MIN(ROW(limiter)),,1,1)))
That gives us this result
Filter the table
How does that work?
Break it down
- 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
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.
You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my book, Going Gas – from VBA to Apps script.