Forum Discussion

darrenwis5's avatar
darrenwis5
Copper Contributor
Oct 25, 2021

Calculate a sum excluding hidden columns, how?

Hi,

I’m trying to calculate a total at the end of a row and I have hidden columns. How do I exclude those hidden columns? I’ve tried doing =subtotal and =aggregate but this didn’t work as I’m trying to exclude a column and not a row.

All advice appreciated.

Thank you.
excel
Formulas and Functions

10 Replies

Sort By
  • Rsartori76's avatar
    Rsartori76
    Copper Contributor
    Jul 20, 2022

    darrenwis5 

    If you don't have too many columns and you don't plan to add any new ones, you can always do a simple SUM(range1,range2,range3,...,rangeN).

     

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 25, 2021

    darrenwis5 

    I am also not a fan of hiding cells as a way of conveying information.  However

    visible
= SIGN(INDEX(CELL("width",(@data)),1))

= SUM(IF(visible,data))

    might work for you.

    Of course, I might use

    = SUM(IF(MAP(data, Visibleλ), data))

where Visibleλ is given by
=LAMBDA(c,SIGN(INDEX(CELL("width",c),1)))

    but that is just showing off how different modern Excel can be.

    • hussain_786_23's avatar
      hussain_786_23
      Copper Contributor
      Jul 20, 2022

      Hi,
      I prefer writing it this way

      N(INDEX(CELL("width",(data)),2))) and avoid the sign function

      I never have used Lambda so Ill need an actual example to understand but ill try
      Is there no other function or conditional formatting which can be used to achieve a value when visible and 0 ( or diff value or text) when hidden

      Love to hear from you

      • lori_m's avatar
        lori_m
        Steel Contributor
        Jul 20, 2022

        hussain_786_23 

        Given N(width) = width, perhaps you mean N(width>0) = SIGN(width) ? 

        For a non-Lambda option maybe try:

        =SUM(IF(CELL("width",OFFSET(data,,SEQUENCE(,COLUMNS(data),0))),data))

  • mathetes's avatar
    mathetes
    Silver Contributor
    Oct 25, 2021

    darrenwis5 

     

    So much--so VERY much--depends on the exact situation.

    Like

    • how many columns are hidden,
    • why are they hidden (if there's a constant condition that could be used in a conditional format, for example)...
    • is the design permanent,
    • could the hidden columns be moved to the right or left of the column where you want to determine the total? 

    Personally, I'd want to think about the implications of each of those questions first. Any other solution would potentially be vulnerable to future changes that make the resulting totals unreliable. A good design tries to reduce the risk of unintended side effects. Hidden columns (which admittedly can be useful) are potential time bombs depending on who uses the resulting spreadsheet, how much they're allowed to do, etc.

Resources