Prev: Changing the range for averages with out changing the formula.
Next: IF and Right Functions
From: Brian on 20 Apr 2010 10:15
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.


From: Bernard Liengme on 20 Apr 2010 10:27
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" <Brian(a)discussions.microsoft.com> wrote in message
news:390AA6D5-5938-4172-B8B4-7CBD78D9059E(a)microsoft.com...
> 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.
>
>
From: Mike H on 20 Apr 2010 10:30
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.
>
>
From: RonaldoOneNil on 20 Apr 2010 10:57
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.
> >
> >
From: Brian on 20 Apr 2010 11:05
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.
> >
> >
 |  Next  |  Last
Pages: 1 2
Prev: Changing the range for averages with out changing the formula.
Next: IF and Right Functions