Summing only #'s on unhidden columns

[Peter Trumbo]

Assume cells B1, C1 and D1 have 5's in them and that I hide column C. I am
trying to figure out how to sum B1:d1 and get the answer 10. When I try to
use the =subtotal(109,b1:d1) function it returns the answer 15.

Subtotal(109,x:y) works to add only shown rows but it doesn't seem to work
on column sums.

Does anybody have a suggestion.

Thanks

Peter

 

[JBeaucaire]

For columns you need a new function. This USER-DEFINED FUNCTION shoul
do the trick

Code
-------------------
Function VisTotal(Rg As Range
Dim x, to
Application.Volatil
tot =
For Each x In R
If x.ColumnWidth = 0 Or x.RowHeight = 0 Then Else tot = tot +
Nex
VisTotal = to
End Function
-------------------

Insert that code into a MODULE, then use the formula simply

=VisTotal(A1:M1

...and only the visible cells across those columns would sum. Adjus
accordingly

 

[Dave Peterson]

Assume cells B1, C1 and D1 have 5's in them and that I hide column C. I am
trying to figure out how to sum B1:d1 and get the answer 10. When I try to
use the =subtotal(109,b1:d1) function it returns the answer 15.

Subtotal(109,x:y) works to add only shown rows but it doesn't seem to work
on column sums.

Does anybody have a suggestion.

Thanks

Peter

 

[Dave Peterson]

If the hidden/visible columns don't change (or don't change very often), you may
want to consider using an indicator row (manually updated).

You could put an x in row 1 in each column that should be visible (and hide row
1).

Then use a formula like:
=sumif($1$1,"x",2:2)
or
=sumif($b$1:$d$1,"x",$b2:$d2)