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