Sum Function Visible Cells only

[Brian]

if column a has the number 1, column b has the number 1 and column c has the
number 1, column d has the function sum(A1:C1) for a total of 3. how can a
change the formula so that I get an answer of 2 when column b is hidden.

 

[Bernard Liengme]

Microsoft has guessed your need:
How to Use a VBA Macro to Sum Only Visible Cells
found at
http://support.microsoft.com/kb/150363
best wishes

 

[Mike H]

Hi,

Unfortunately the ideal solution SUBTOTAL only works on columns and not rows
so how about a User Defined Function.

Alt+F11 to open VB Editor. Right click 'ThisWorkbook' and 'Insert module'
and paste this code in

Call with this formula on the worksheet

=sumvis(A1:C1)



Function sumvis(rng As Range)
For Each c In rng
If c.ColumnWidth > 0 Then
If IsNumeric(c) Then
sumvis = sumvis + c.Value
End If
End If
Next
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[RonaldoOneNil]

I tried something similar and it almost works. I have my spreadsheet set to
automatic calculation but it does not recalculate when I hide or unhide my
column. Even if I press F9, it does not recalculate. I have to go into the
cell with the UDF formula and click the tick on the formula bar before it
recalculates.

 

[Brian]

Thank you, it worked perfectly.

Hi,

Unfortunately the ideal solution SUBTOTAL only works on columns and not rows
so how about a User Defined Function.

Alt+F11 to open VB Editor. Right click 'ThisWorkbook' and 'Insert module'
and paste this code in

Call with this formula on the worksheet

=sumvis(A1:C1)



Function sumvis(rng As Range)
For Each c In rng
If c.ColumnWidth > 0 Then
If IsNumeric(c) Then
sumvis = sumvis + c.Value
End If
End If
Next
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Hiding columns does not trigger calculation but if you put

application.volatile

at the start of the UDF it will recalculate when Excel does
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.