Announcement

Collapse
No announcement yet.

Sum Visible Cells Only

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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

    Comment


    • #3
      Re: Sum Visible Cells Only

      Richard, you are awesome! That works perfectly and thanks so much!

      Comment


      • #4
        Re: Sum Visible Cells Only

        Be aware that it will still sum hidden columns.

        Wonder how that happened ...
        Entia non sunt multiplicanda sine necessitate.

        Comment


        • #5
          Re: Sum Visible Cells Only

          Originally posted by 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?

          Comment


          • #6
            Re: Sum Visible Cells Only

            Like this, I suspect:

            "OMG! I forgot about columns!"

            "Relax, it's a feature ..."
            Entia non sunt multiplicanda sine necessitate.

            Comment


            • #7


              Re: Sum Visible Cells Only

              I'd put money on you being right there!

              Comment

              Working...
              X