MrExcel Publishing
Your One Stop for Excel Tips & Solutions

autofilter question


Posted by Kevin on November 02, 2001 5:48 AM

I am using autofilter to filter out a column of dollar amounts based on certain criteria. I also have a cell that sums the column. When I filter out certain rows in the column, they are still included in the column total in my other cell's formula. Is there any way that I can sum only the rows in the column that have not been filtered out with autofilter?

Thanks in advance,
Kevin


Posted by Juan Pablo on November 02, 2001 6:09 AM

Instead of using the SUM function, use the SUBTOTAL function, like this.

If your current formula is:

=SUM(A2:A200)

Change it to

=SUBTOTAL(9,A2:A200)

Subtotal function only refers to "visible" cells.

Juan Pablo

Posted by Kevin on November 02, 2001 6:16 AM

thank you very much