excel formula to sum only visible columns or rows
Is there a formula that would sum only the visible columns or rows in an excel spreadsheet. I have the numbers 4 in A1 2 in B1 3 in C1 and 1 in D1 I hide columns A and D the answer in E1 should be 5. If I unhide column A the answer in E1 would change to 9. I realize the formula would be somewhat the same for rows.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
cains,
Would it be too much trouble to use an extra row?
1. Enter numbers in A1:E1
2. Enter this formula in A2: =CELL("width",A1)
3. Copy the formula from A2 all the way to E2.
4. Use this formula in F1:
=SUM(IF(A2:E2>0,A1:E1,0))
It's an array formula so you must press Ctrl+Shift+Enter afterwards.
Done.
Unfortunately, the formula isn't recalculated automatically when you hide/unhide columns. It will recalculate when you type anything to the sheet or if you press F9 (the "calculate now!" button)
Ture Magnusson
Karlstad, Sweden
Would it be too much trouble to use an extra row?
1. Enter numbers in A1:E1
2. Enter this formula in A2: =CELL("width",A1)
3. Copy the formula from A2 all the way to E2.
4. Use this formula in F1:
=SUM(IF(A2:E2>0,A1:E1,0))
It's an array formula so you must press Ctrl+Shift+Enter afterwards.
Done.
Unfortunately, the formula isn't recalculated automatically when you hide/unhide columns. It will recalculate when you type anything to the sheet or if you press F9 (the "calculate now!" button)
Ture Magnusson
Karlstad, Sweden
If you add the line (to vboukhar' s function)
Application.Volatile
right after the lines
Public Function SumVisible(a As Range) As Double
Dim c As Range
Dim value As Double
that should take care of the problem. This causes it to recaculate automatically.
Application.Volatile
right after the lines
Public Function SumVisible(a As Range) As Double
Dim c As Range
Dim value As Double
that should take care of the problem. This causes it to recaculate automatically.
ASKER
ture nope can't add row or column. Thanks Anyhu
blakeh1 that works if I press F9 but I'm still hopeing it will recalculate automatically.
Thanks so far tho
blakeh1 that works if I press F9 but I'm still hopeing it will recalculate automatically.
Thanks so far tho
IF you hide *rows* by filtering, then you could use the SUBTOTAL function_num 9. For details see Excel help.
As for VBouhkAr's macro, I would pass the summands/sumrange as ranges to the function and not as string, otherwise inserting a row/colum 'above them" will foul up the function call.
As for VBouhkAr's macro, I would pass the summands/sumrange as ranges to the function and not as string, otherwise inserting a row/colum 'above them" will foul up the function call.
ASKER
Cri due to the structure of the spreadsheet I'm not able to use a filter, mind you not from the lack of trying to. I am using vbouhkar's solution along with blakeh1's suggestion and simply added a macro button for the refresh command. So how do we awards points for 2 answers?
Ps good to hear from you agin CRI
Ps good to hear from you agin CRI
ASKER
vboukhar best answered the question from my perspective anyway so I'm awarding the points him er her. Thanks also for your comments blake1 and cri
ASKER