Calculate a sum excluding hidden columns, how?

%3CLINGO-SUB%20id%3D%22lingo-sub-2882842%22%20slang%3D%22en-US%22%3ECalculate%20a%20sum%20excluding%20hidden%20columns%2C%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2882842%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%E2%80%99m%20trying%20to%20calculate%20a%20total%20at%20the%20end%20of%20a%20row%20and%20I%20have%20hidden%20columns.%20How%20do%20I%20exclude%20those%20hidden%20columns%3F%20I%E2%80%99ve%20tried%20doing%20%3Dsubtotal%20and%20%3Daggregate%20but%20this%20didn%E2%80%99t%20work%20as%20I%E2%80%99m%20trying%20to%20exclude%20a%20column%20and%20not%20a%20row.%3CBR%20%2F%3E%3CBR%20%2F%3EAll%20advice%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2882842%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2883294%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20a%20sum%20excluding%20hidden%20columns%2C%20how%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2883294%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1195576%22%20target%3D%22_blank%22%3E%40darrenwis5%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20much--so%20VERY%20much--depends%20on%20the%20exact%20situation.%3C%2FP%3E%3CP%3ELike%3C%2FP%3E%3CUL%3E%3CLI%3Ehow%20many%20columns%20are%20hidden%2C%3C%2FLI%3E%3CLI%3Ewhy%20are%20they%20hidden%20(if%20there's%20a%20constant%20condition%20that%20could%20be%20used%20in%20a%20conditional%20format%2C%20for%20example)...%3C%2FLI%3E%3CLI%3Eis%20the%20design%20permanent%2C%3C%2FLI%3E%3CLI%3Ecould%20the%20hidden%20columns%20be%20moved%20to%20the%20right%20or%20left%20of%20the%20column%20where%20you%20want%20to%20determine%20the%20total%3F%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3CP%3EPersonally%2C%20I'd%20want%20to%20think%20about%20the%20implications%20of%20each%20of%20those%20questions%20first.%20Any%20other%20solution%20would%20potentially%20be%20vulnerable%20to%20future%20changes%20that%20make%20the%20resulting%20totals%20unreliable.%20A%20good%20design%20tries%20to%20reduce%20the%20risk%20of%20unintended%20side%20effects.%20Hidden%20columns%20(which%20admittedly%20can%20be%20useful)%20are%20potential%20time%20bombs%20depending%20on%20who%20uses%20the%20resulting%20spreadsheet%2C%20how%20much%20they're%20allowed%20to%20do%2C%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
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 Replies

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

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