Forum Discussion
Calculate a sum excluding hidden columns, how?
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.
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 youGiven 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))
Given N(width) = width, perhaps you mean N(width>0) = SIGN(width) ?
In short no but that works tooWhat I actually was trying to do is
when I try only CELL("width",data) in excel 21 then I get SPILL errors and the formula doesn't work because it evaluates to an array say {8,FALSE}
Therefore the index function becomes necessary
in Excel 21 INDEX(CELL("width",(data)),2)) gives me TRUE or FALSE as applicable depending on the width of the column
If the column is hidden then it evaluates to FALSE
N Function then converts it to 1 or 0
Instead as PeterBartholomew1 suggests we can use
INDEX(CELL("width",(@data)),1) instead which would give the width of the column which is converted to 1 or 0 using the SIGN function
Thanks for the non lambda suggestion!
