How to ignore hidden columns

  • Thread starter Thread starter ismae
  • Start date Start date
I

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need array formula to ignore hidden values 1
How do you ignore hidden rows in a countif() function 9
Subtoal excluding hidden columns 7
Subtotal across a row 1
Adding totals in columns 2
How do I avoid referencing hidden values in formulas like OFFSET? 2
Ignore Hidden Rows and Count of distinct values 3
Average without Hidden Cells 4

Back
Top