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.
Sum Visible Cells Only
-
-
-
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
Richard, you are awesome! That works perfectly and thanks so much!
-
Re: Sum Visible Cells Only
Be aware that it will still sum hidden columns.
Wonder how that happened ...
-
Re: Sum Visible Cells Only
Quote from shgBe 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?
-
-
Re: Sum Visible Cells Only
Like this, I suspect:
"OMG! I forgot about columns!"
"Relax, it's a feature ..."
-
Re: Sum Visible Cells Only
I'd put money on you being right there! :lol:
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!