Need to sum columns, excluding hidden columns.

[Guest]

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?

 

[Biff]

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

 

[Guest]

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

 

[Pete_UK]

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

 

[Biff]

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

 

[Biff]

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

 

[Roger Govier]

XL2003
I can confirm that Subtotal of both types (9 and 109) only works on
Rows, not on Columns

 

[Pete_UK]

Ah well, it was worth a try! Thanks for the confirmation, Roger.

I agree, Biff - half-assed attempt indeed.

Pete