| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to sum columns, excluding hidden columns.
I need to sum a row spanning several columns where some of the columns are
hidden. I need to have the hidden columns excluded from the total. Is there a function that will do that similar to how 'subtotal' works for rows? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to sum columns, excluding hidden columns.
Here's a kludge....
Suppose the range in question is A1:E1. You need helper cells to determine if the column is hidden**. Enter this formula somewhere, say, A2 and copy across to E2: =CELL("width",A1)0 Then, to get the sum of the visible** columns: =SUMIF(A2:E2,TRUE,A1:E1) ** hiding or unhiding rows/columns does not trigger a calculation so the formula will not update when the columns are hidden or unhidden. You'll have to either force a calculation (hit function key F9) or wait unitl some other event triggers a calculation. Like I said, kludge! But it's better than nothing!! (maybe!) Biff "psill" wrote in message ... I need to sum a row spanning several columns where some of the columns are hidden. I need to have the hidden columns excluded from the total. Is there a function that will do that similar to how 'subtotal' works for rows? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to sum columns, excluding hidden columns.
Biff....
Thank you. At least it's nice to know that I wasn't losing my mind because I couldn't find a function to do it neatly. But I'll take it anyway I can get it. Thanks again! psill "Biff" wrote: Here's a kludge.... Suppose the range in question is A1:E1. You need helper cells to determine if the column is hidden**. Enter this formula somewhere, say, A2 and copy across to E2: =CELL("width",A1)0 Then, to get the sum of the visible** columns: =SUMIF(A2:E2,TRUE,A1:E1) ** hiding or unhiding rows/columns does not trigger a calculation so the formula will not update when the columns are hidden or unhidden. You'll have to either force a calculation (hit function key F9) or wait unitl some other event triggers a calculation. Like I said, kludge! But it's better than nothing!! (maybe!) Biff "psill" wrote in message ... I need to sum a row spanning several columns where some of the columns are hidden. I need to have the hidden columns excluded from the total. Is there a function that will do that similar to how 'subtotal' works for rows? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to sum columns, excluding hidden columns.
I don't have XL2003 so I can't try this out, but see if this works:
=SUBTOTAL(109,A2:E2) I've read in other posts that by adding 100 to the first parameter this can ignore hidden cells. Hope this helps. Pete psill wrote: Biff.... Thank you. At least it's nice to know that I wasn't losing my mind because I couldn't find a function to do it neatly. But I'll take it anyway I can get it. Thanks again! psill "Biff" wrote: Here's a kludge.... Suppose the range in question is A1:E1. You need helper cells to determine if the column is hidden**. Enter this formula somewhere, say, A2 and copy across to E2: =CELL("width",A1)0 Then, to get the sum of the visible** columns: =SUMIF(A2:E2,TRUE,A1:E1) ** hiding or unhiding rows/columns does not trigger a calculation so the formula will not update when the columns are hidden or unhidden. You'll have to either force a calculation (hit function key F9) or wait unitl some other event triggers a calculation. Like I said, kludge! But it's better than nothing!! (maybe!) Biff "psill" wrote in message ... I need to sum a row spanning several columns where some of the columns are hidden. I need to have the hidden columns excluded from the total. Is there a function that will do that similar to how 'subtotal' works for rows? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to sum columns, excluding hidden columns.
I also don't have XL 2003 but I've read posts where the 100 series arguments
only work on rows and not columns. Biff "Pete_UK" wrote in message oups.com... I don't have XL2003 so I can't try this out, but see if this works: =SUBTOTAL(109,A2:E2) I've read in other posts that by adding 100 to the first parameter this can ignore hidden cells. Hope this helps. Pete psill wrote: Biff.... Thank you. At least it's nice to know that I wasn't losing my mind because I couldn't find a function to do it neatly. But I'll take it anyway I can get it. Thanks again! psill "Biff" wrote: Here's a kludge.... Suppose the range in question is A1:E1. You need helper cells to determine if the column is hidden**. Enter this formula somewhere, say, A2 and copy across to E2: =CELL("width",A1)0 Then, to get the sum of the visible** columns: =SUMIF(A2:E2,TRUE,A1:E1) ** hiding or unhiding rows/columns does not trigger a calculation so the formula will not update when the columns are hidden or unhidden. You'll have to either force a calculation (hit function key F9) or wait unitl some other event triggers a calculation. Like I said, kludge! But it's better than nothing!! (maybe!) Biff "psill" wrote in message ... I need to sum a row spanning several columns where some of the columns are hidden. I need to have the hidden columns excluded from the total. Is there a function that will do that similar to how 'subtotal' works for rows? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to sum columns, excluding hidden columns.
You're welcome! You'd think this should be easier to accomplish. There's
"width" argument but no "height" argument. Subtotal works on rows but not columns (XL2003). Everything seems like a half-assed attempt by MS. Biff "psill" wrote in message ... Biff.... Thank you. At least it's nice to know that I wasn't losing my mind because I couldn't find a function to do it neatly. But I'll take it anyway I can get it. Thanks again! psill "Biff" wrote: Here's a kludge.... Suppose the range in question is A1:E1. You need helper cells to determine if the column is hidden**. Enter this formula somewhere, say, A2 and copy across to E2: =CELL("width",A1)0 Then, to get the sum of the visible** columns: =SUMIF(A2:E2,TRUE,A1:E1) ** hiding or unhiding rows/columns does not trigger a calculation so the formula will not update when the columns are hidden or unhidden. You'll have to either force a calculation (hit function key F9) or wait unitl some other event triggers a calculation. Like I said, kludge! But it's better than nothing!! (maybe!) Biff "psill" wrote in message ... I need to sum a row spanning several columns where some of the columns are hidden. I need to have the hidden columns excluded from the total. Is there a function that will do that similar to how 'subtotal' works for rows? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to sum columns, excluding hidden columns.
XL2003
I can confirm that Subtotal of both types (9 and 109) only works on Rows, not on Columns -- Regards Roger Govier "Biff" wrote in message ... I also don't have XL 2003 but I've read posts where the 100 series arguments only work on rows and not columns. Biff "Pete_UK" wrote in message oups.com... I don't have XL2003 so I can't try this out, but see if this works: =SUBTOTAL(109,A2:E2) I've read in other posts that by adding 100 to the first parameter this can ignore hidden cells. Hope this helps. Pete psill wrote: Biff.... Thank you. At least it's nice to know that I wasn't losing my mind because I couldn't find a function to do it neatly. But I'll take it anyway I can get it. Thanks again! psill "Biff" wrote: Here's a kludge.... Suppose the range in question is A1:E1. You need helper cells to determine if the column is hidden**. Enter this formula somewhere, say, A2 and copy across to E2: =CELL("width",A1)0 Then, to get the sum of the visible** columns: =SUMIF(A2:E2,TRUE,A1:E1) ** hiding or unhiding rows/columns does not trigger a calculation so the formula will not update when the columns are hidden or unhidden. You'll have to either force a calculation (hit function key F9) or wait unitl some other event triggers a calculation. Like I said, kludge! But it's better than nothing!! (maybe!) Biff "psill" wrote in message ... I need to sum a row spanning several columns where some of the columns are hidden. I need to have the hidden columns excluded from the total. Is there a function that will do that similar to how 'subtotal' works for rows? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to sum columns, excluding hidden columns.
Ah well, it was worth a try! Thanks for the confirmation, Roger.
I agree, Biff - half-assed attempt indeed. Pete Roger Govier wrote: XL2003 I can confirm that Subtotal of both types (9 and 109) only works on Rows, not on Columns -- Regards Roger Govier "Biff" wrote in message ... I also don't have XL 2003 but I've read posts where the 100 series arguments only work on rows and not columns. Biff "Pete_UK" wrote in message oups.com... I don't have XL2003 so I can't try this out, but see if this works: =SUBTOTAL(109,A2:E2) I've read in other posts that by adding 100 to the first parameter this can ignore hidden cells. Hope this helps. Pete psill wrote: Biff.... Thank you. At least it's nice to know that I wasn't losing my mind because I couldn't find a function to do it neatly. But I'll take it anyway I can get it. Thanks again! psill "Biff" wrote: Here's a kludge.... Suppose the range in question is A1:E1. You need helper cells to determine if the column is hidden**. Enter this formula somewhere, say, A2 and copy across to E2: =CELL("width",A1)0 Then, to get the sum of the visible** columns: =SUMIF(A2:E2,TRUE,A1:E1) ** hiding or unhiding rows/columns does not trigger a calculation so the formula will not update when the columns are hidden or unhidden. You'll have to either force a calculation (hit function key F9) or wait unitl some other event triggers a calculation. Like I said, kludge! But it's better than nothing!! (maybe!) Biff "psill" wrote in message ... I need to sum a row spanning several columns where some of the columns are hidden. I need to have the hidden columns excluded from the total. Is there a function that will do that similar to how 'subtotal' works for rows? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to keep hidden columns hidden using protection | Excel Discussion (Misc queries) | |||
| how do i copy formula down columns avoiding hidden cells | Excel Discussion (Misc queries) | |||
| PLEASE HELP - Pasting Cells to WrkSheet with Hidden Columns | Excel Worksheet Functions | |||
| How do I lock hidden columns? | Excel Worksheet Functions | |||
| I can't unhide 3 columns previously hidden | Excel Discussion (Misc queries) | |||
Linear Mode
