how do I sum only visible data in a column
I have some rows manually hidden, Please is there a formula I can apply to
return the sum of the unhidden data contain in a column? |
Hi Afolabi,
If you don't want to use VBA (create a UDF), you could review the following pages: http://www.jkp-ads.com/Articles/ExcelNames08.htm http://www.jkp-ads.com/Articles/ExcelNames09.htm In any case, there is a fundamental issue with both approaches: since hiding/unhiding rows doesn't trigger any event, such event will have to be forced from time to time, or maybe you can leave with the formulae updating their result in the next recalculation of the sheet. Regards, KL "Afolabi" wrote in message ... I have some rows manually hidden, Please is there a formula I can apply to return the sum of the unhidden data contain in a column? |
Depending upon your needs, there is a Copy of visible only cells, click Edit, Go To, select Visible Cells, then select the range and paste. This new range can then be totalled. Otherwise . . I think it's a little VBA code -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=390100 |
If you're using xl2003, you can use =subtotal(). This worksheet function has
been enhanced to include options to ignore manually hidden rows. =subtotal(109,a2:a22) for instance. Afolabi wrote: I have some rows manually hidden, Please is there a formula I can apply to return the sum of the unhidden data contain in a column? -- Dave Peterson |
All times are GMT +1. The time now is 07:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com