Autofilter doesn't work with SUBTOTAL formula

**jrdegraaff** · 08-16-2010, 04:17 AM

Hi,

I have a problem with Autofilter, when I put a SUBTOTAL formula in the rows of the data to be filtered. Every time I filter (on a column which doesn't have the SUBTOTAL formula in it), it takes into account only the range of the data minus 1 row.

It has obviously something to do with the SUBTOTAL formula, because if I remove it filtering works correctly.

See attached sheet...Does anybody have a solution for this?

**romperstomper** · 08-16-2010, 04:26 AM

Not if you want to keep the table. If you convert to a range, it will work. SUBTOTAL is not really intended for use with columns, only rows, so the table automatically treats the last row as though it were a totals row.

**jrdegraaff** · 08-16-2010, 04:30 AM

If I convert to a range, I still have the same problem, please check my sheet and convert the table back to a range. You will see it doesn't work.

**romperstomper** · 08-16-2010, 04:34 AM

Sorry, I forgot that I had added the Totals row before I did the conversion. You need to have a row at the end that does not use the SUBTOTAL function.