Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Sum Visible Cells Only

  1. #1
    Join Date
    2nd July 2004
    Posts
    81

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    2nd July 2004
    Posts
    81

    Re: Sum Visible Cells Only

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    Re: Sum Visible Cells Only

    Be aware that it will still sum hidden columns.

    Wonder how that happened ...
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  5. #5
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Sum Visible Cells Only

    Quote 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?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    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.
    MS MVP - Excel

  7. #7
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Sum Visible Cells Only

    I'd put money on you being right there!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Move Cells Into Visible Cells
    By Keilcentre in forum EXCEL HELP
    Replies: 4
    Last Post: July 17th, 2007, 18:24
  2. Text In Cells Not Visible
    By jillyb in forum EXCEL HELP
    Replies: 12
    Last Post: May 24th, 2007, 04:13
  3. Paste Visible Cells Only
    By simoncreighton in forum EXCEL HELP
    Replies: 3
    Last Post: February 28th, 2007, 20:27
  4. VBA: Visible Cells
    By Gary in forum EXCEL HELP
    Replies: 17
    Last Post: July 8th, 2003, 22:39
  5. Visible Cells
    By Ranger in forum EXCEL HELP
    Replies: 1
    Last Post: April 5th, 2003, 02:54

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno