a way sum only columns not hidden?
 

I have a worksheet with many hidden columns. The columns I hide keep
changing. Is there a way to have column totals adjust to sum only the
columns showing?

TIA

Todd

Hi
only possible with VBA.
SUBTOTAL would work if you would hide rows with a filter

--
Regards
Frank Kabel
Frankfurt, Germany

"Todd" schrieb im Newsbeitrag
...
I have a worksheet with many hidden columns. The columns I hide keep
changing. Is there a way to have column totals adjust to sum only
the
columns showing?

TIA

Todd

I am sure there are better ways, but if not then you can try this UDF:

Function SumVisible(mySelection As Range)
Application.Volatile
For Each c In mySelection
If c.Rows(c.Row).Hidden = False And _
IsNumeric(c) Then mySum = mySum + c.Value
Next c
SumVisible = mySum
End Function

Regards,
KL

"Todd" wrote in message
...
I have a worksheet with many hidden columns. The columns I hide keep
changing. Is there a way to have column totals adjust to sum only the
columns showing?

TIA

Todd

SUBTOTAL in 2003 will work for manually hiiden rows as well

=SUBTOTAL(109,Range)

for hidden rows

=SUBTOTAL(3,Range)

for filtered rows

Regards,

Peo Sjoblom

"Frank Kabel" wrote:

Hi
only possible with VBA.
SUBTOTAL would work if you would hide rows with a filter

--
Regards
Frank Kabel
Frankfurt, Germany

"Todd" schrieb im Newsbeitrag
...
I have a worksheet with many hidden columns. The columns I hide keep
changing. Is there a way to have column totals adjust to sum only
the
columns showing?

TIA

Todd

Hi Peo
agreed but I think the OP asked for hidden columns and for this it
would not work

--
Regards
Frank Kabel
Frankfurt, Germany

"Peo Sjoblom" schrieb im
Newsbeitrag ...
SUBTOTAL in 2003 will work for manually hiiden rows as well

=SUBTOTAL(109,Range)

for hidden rows

=SUBTOTAL(3,Range)

for filtered rows

Regards,

Peo Sjoblom

"Frank Kabel" wrote:

Hi
only possible with VBA.
SUBTOTAL would work if you would hide rows with a filter

--
Regards
Frank Kabel
Frankfurt, Germany

"Todd" schrieb im Newsbeitrag
...
I have a worksheet with many hidden columns. The columns I hide
keep
changing. Is there a way to have column totals adjust to sum
only
the
columns showing?

TIA

Todd