Archive of Mr Excel Message Board

Back to Data in Excel archive index
Back to archive home

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

Re: autofilter question

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

thank you very much

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.