MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sum only visible cells


Posted by Gaby on December 07, 2001 11:39 AM

I want to be able to sum visible cells. Ideally, the macro to do this would not need to be told the column, it would instead sum everything visible above the selected cell, whatever that may be at any given time. Is this possible? Thanks


Posted by jack on December 07, 2001 11:47 AM

Posted by Mark W. on December 07, 2001 11:51 AM

Excel's SUBTOTAL worksheet function provides this
functionality if the rows are hidden via AutoFiltering.

Posted by jack on December 07, 2001 11:52 AM

use =subtotal(9,A:A)put this in any non Col A cell,the 9 does what you ask.

WARNING counts hidden cells, so i guess you use auto-filter, so if you filter out all the X then total will = all minus X

BTW This is what you waht, i have auto cal OFF so i hit F9 to cal as i need
HTH