Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Visible Cells in Sheet with Merged and Hidden Cells | Excel Discussion (Misc queries) | |||
Function for visible cells only | Excel Worksheet Functions | |||
Performing a function on visible cells only in a range | Excel Discussion (Misc queries) | |||
In Excel - a function or operation for "view visible cells" | Excel Discussion (Misc queries) | |||
Visible cells | Excel Worksheet Functions |