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

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


Code:
--------------------
Function VisTotal(Rg As Range)
Dim x, tot
Application.Volatile
tot = 0
For Each x In Rg
If x.ColumnWidth = 0 Or x.RowHeight = 0 Then Else tot = tot + x
Next
VisTotal = tot
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. Adjust
accordingly.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46479

Peter Trumbo wrote:

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)



Peter Trumbo wrote:

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