how do I sum only visible data in a column

[Guest]

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?

 

[KL]

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

 

[Bryan Hessey]

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

 

[Dave Peterson]

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.