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?

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?

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?

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?

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?

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?

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?

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?