Sum Function Visible Cells only

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.

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Brian" wrote in message
...
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.


"Brian" wrote:

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.

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.

"Mike H" wrote:

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.


"Brian" wrote:

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.

Thank you, it worked perfectly.

"Mike H" wrote:

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.


"Brian" wrote:

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.

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.


"RonaldoOneNil" wrote:

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.

"Mike H" wrote:

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.


"Brian" wrote:

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.