Can the formula calculation depend upon the visibility of rows?

Posted by: loychan-fp on 8 September 2017, 2:12 pm EST

  • Posted 8 September 2017, 2:12 pm EST

    I have a situation where i have to sum of a list of values for two departments.

    The list contains all the employees in both departments and I have a sum at the bottom of the column (using SUM function). This all works fine but I have a department filter and I want this sum to reflect the sum of the non-filtered rows. Note that I had to change the totals rows once the filter is applied so that it matches the filter string so the totals row will show up.

    I can do this with some manual coding but since I have many of these totals on the sheet, I'm worried about the performance. So is there was a way the SUM function can ignore hidden cells with the specified range?

    Thanks

    Loy

  • Replied 8 September 2017, 2:12 pm EST

    Hmmm ... after some googling, I thought I might be able to use the SUBTOTAL function but it doesn't seem to work.  I'm unsure if it has to do with the fact I'm using R1C1 reference or if SUBTOTAL is not meant for what I'm hoping for.
  • Replied 8 September 2017, 2:12 pm EST

    Hello LoyChan,


    We can achieve this by creating a hidden column in the Spread that has the same data as the column that has the data being filtered, except for the row with the subtotal. Then, programatically filter the hidden column. You can find attached  sample for the same and a reference link wherein Scott described the same behaviour.


    Hope this will help. Thanks.


    2010/01/WindowsApplication3.zip
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels