How to ignore hidden columns
 

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?

How to ignore hidden columns
 

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" wrote:

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?

How to ignore hidden columns
 

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.


"Mike H" wrote:

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" wrote:

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?

How to ignore hidden columns
 

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

On Dec 17, 11:03 am, ismae wrote:
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.



"Mike H" wrote:
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" wrote:

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?- Hide quoted text -

- Show quoted text -