Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Combination VBA / Formulation trick to sum Visible cells onl

  1. #1
    Board Regular Chris The Rock's Avatar
    Join Date
    Feb 2002
    Location
    Longmont, CO
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Setup:
    I've got 31 days worth of data in rows 4 through 33, and a Totals Row in row 34. Trows that correspond to days that have not occurred yet are hidden. Each morning, I unhide a new row and data goes into each of the columns. As a result of this, the Totals row changes.

    Question:
    What I want to do is this: I would like the Totals line to perform functions ONLY on the visible rows. For example, if Rows 6 - 33 are hidden, I'd like the TOTALS row (row 34) to perform its functions (SUM, AVERAGE, etc) on ONLY ROWS 4 and 5. Likewise, if all the rows between 4 and 33 are visible, it should perform functions on ALL the rows.

    How can I accomplish this? My first thought is to establish some sort of TRUE / FALSE variable for each row that tells whether the row is hidden or not. Then, I could use SUMIF-style formulas. But, alas, I don't know how to do this, or even if it will work or not.

    "Never go in against a Sicilian when death is on the line!"

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm assuming that you have a column that has the date in it as well. If that's the case, I'd suggest using the Autofilter feature on the date column (Data|Filter|Autofilter) combined with the "SUBTOTAL" function.

    The criteria for Autofilter would be "is less than" the first date you want to be invisible.

    If you then look in the help file you will see that SUBTOTAL can do AVERAGE, SUM, etc on data that is hidden only by using Autofilter.

    HTH

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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