Calculate a sum excluding hidden columns, how?

[darrenwis5]

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.

[#2]

@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.

[#3]

@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.

[#4]

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

[#5]

@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).

[#6]

@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))

[#7]

Given N(width) = width, perhaps you mean N(width>0) = SIGN(width) ?
In short no but that works too 

What 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 @Peter Bartholomew 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!

[#8]

@hussain_786_23 

According to MS Help on CELL("width") :

"Returns an array with 2 items.

The 1st item in the array is the column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.

The 2nd item in the array is a Boolean value, the value is TRUE if the column width is the default or FALSE if the width has been explicitly set by the user. 

Note: This value is not supported in Excel for the web, Excel Mobile, and Excel Starter."

This suggests the 2nd item is not a reliable check for column visibility though may sometimes coincide. The 1st item isn't totally reliable either as very small column widths can get rounded to zero. For windows versions the only non-vba way to get the exact width seems to be:

ColumnWidth=LAMBDA(range,GET.CELL(16,range))

[#9]

@lori_m Thanks for picking this up; I have been tied up with other things today.  A good thing as well, because I misinterpreted the question.  I assumed an alternative was required for 'CELL'; the idea of trying to remove LAMBDA, at a time when everything I do is aimed at exploiting Lambda, failed to register correctly!

[#10]

Apologies for jumping in, and i don't think I fully addressed the question either! My response was mainly prompted by the proposed N() formulation which appeared to have some shortcomings.

As for MAP versus native array processing... that's entirely personal preference, I guess I tend to be influenced by NumPy style in this respect.

[#11]

Thanks Lori !
The drawbacks of using CELL function for finding width weren't obvious but we're nicely highlighted by you