The problem is that if you want formulas which adapt to filtering, it may have to be slow.
Or maybe not. I can't find any robust way to handle this with 1 formula per result. With only 2 formulas per result, it is indeed quite slow due to needing to find the nearest previous visible row. Looks like efficiency requires 3 formulas per result: one for the SUBTOTAL call to tell whether the row is hidden or not, another to hold the last visible col A value, and the col C values.
C2: =D2
C3: =C2+D3*(A3<>E2)
Fill C3 down as far as needed.
D2: =SUBTOTAL(3,A2)
E2: =IF(D2,A2,E1)
Select D2:E2 and fill down as far as needed. Filter cols A:B, and col C values should be correct.
I've updated my copy of your sample workbook
here on OneDrive. I added a worksheet named
lots of data, which has 100,000 rows of made-up data and categories 1-4 in col B on which to filter. There's still some lag, but I figure it's unavoidable for as many calculations as are performed.
Bookmarks