If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Sum Function Visible Cells only
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. |
#3
|
|||
|
|||
Sum Function Visible Cells only
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. |
#4
|
|||
|
|||
Sum Function Visible Cells only
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. |
#5
|
|||
|
|||
Sum Function Visible Cells only
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. |
#6
|
|||
|
|||
Sum Function Visible Cells only
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. |
Thread Tools | |
Display Modes | |
|
|