Sum Function Visible Cells only

  • Thread starter Thread starter Brian
  • Start date Start date
B

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.
 
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.
 
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.
 
Thank you, it worked perfectly.

Mike H said:
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.
Click to expand...
 
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.
 
Back
Top