Sum Visible Cells Only

    I have various spreadsheets that have rows hidden with data that I do not want included in the totals. Because of multiple header lines in the worksheets, the use of filters is not practical. Is there a formula that I can use that will sum only the visible cells? Thanks in advance for any help on this.

    Re: Sum Visible Cells Only


    Hi Keith


    Check out the Excel Help on SUBTOTAL - if you have hidden these rows through normal hiding (ie rather than thru Autofilter) I think you need:


    =SUBTOTAL(109,YourRange)


    but confirm this in the excel help first.


    Best regards


    Richard

    Re: Sum Visible Cells Only


    Quote from shg

    Be aware that it will still sum hidden columns.


    Wonder how that happened ...


    Yes - it does make you wonder why MS would design it that way. Fair enough that a traditional data structure dictates fields in columns and records in rows, but really was there any need to include hidden columns?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login