| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Functions to ignore hidden rows | Excel Worksheet Functions | |||
| how to ignore hidden cells with a countif | Excel Worksheet Functions | |||
| How do I ignore values while copying from hidden rows? | Excel Worksheet Functions | |||
| How to "ignore" hidden columns? | Excel Discussion (Misc queries) | |||
| Ignore Hidden Rows in Sum Function? | Excel Discussion (Misc queries) | |||
Linear Mode
