Solved

excel formula to sum only visible columns or rows

Posted on 2002-03-06
8
926 Views
Last Modified: 2012-07-09
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.
0
Comment
Question by:cains
8 Comments
 
LVL 5

Accepted Solution

by:
vboukhar earned 200 total points
ID: 6846017
Hi cains,

You can use user-defined function.
Press Alt-F11 (open VBA editor), add module and copy-paste next code:
Public Function SumVisible(a As Range) As Double
    Dim c As Range
    Dim value As Double
    value = 0
   
    For Each c In a
        If c.EntireColumn.Hidden = False And c.EntireRow.Hidden = False Then
            If IsNumeric(c.value) = True Then
                value = value + c.value
            End If
        End If
    Next
    SumVisible = value
End Function

after that on your sheet you can use this function:
=SumVisible("A1:D1")
=SumVisible("B1:B20")

Hope it hepls
0
 

Author Comment

by:cains
ID: 6846300
Okay that works but...with the formula (had to take out the " ")there it sums the row until I hide 1 column and to update the answer if have to delete the formula and paste it back in again.  Tried recalculate after hiding the column but no luck.  This is in excel 2000 if that helps.  What I need is for the formula to recalculate when I hide another column,  Is that possible?
0
 
LVL 22

Expert Comment

by:ture
ID: 6846804
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
0
 
LVL 6

Expert Comment

by:blakeh1
ID: 6848054
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:cains
ID: 6848362
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
0
 
LVL 13

Expert Comment

by:cri
ID: 6853109
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.

0
 

Author Comment

by:cains
ID: 6853249
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
0
 

Author Comment

by:cains
ID: 6892108
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now