How to ignore hidden columns

[ismae]

I'd like to sum values in a row, ignoring the values in hidden columns. I
tried the SUBTOTAL function, but it appears to work only with hidden rows,
not columns. Any ideas?

 

[Mike H]

This should be simple but in the meantime try this

Say your data to sum are in A1 - J1
Put this in A2 and drag across to J2 ensuring it goes in your hidden columns.
=CELL("width",A1)>0
Then hide your columns and sum with this formula
=SUMIF(A2:J2,TRUE,A1:J1)

Mike

 

[ismae]

Thank you for your reply, Mike. I see your suggestion, but I'd looking for a
simpler solution, since I need to repeat this formula in more than a hundred
rows.

 

[Dave F]

Modify the formula to this:

=SUMIF(A$2:J$2,TRUE,A1:J1)

Then you can fill down hundreds or thousands of rows without having to
manually change the formula.

Dave