?
Solved

Sum Visible Columns or Rows in Range

Posted on 2003-03-07
8
Medium Priority
?
970 Views
Last Modified: 2012-08-14
I came across this user defined fuction on this site.  I would like to perhaps modify the below function to work like the SUM function.  For example, if my formula says =Sumvisible(E2, F2, H2, L2), it would add those cells that are visible.  If I hide Column F, then my formula should recalculate with E2, H2, and L2.  Is this possible or is there a better solution?

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")

0
Comment
Question by:Grubeaters
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8090972
The function you posted already does just that.

The only thing I'm finding strange is that it doesn't recalculate when you hide a column. Not even if I press F9. Only when I change one of the values in the range or even the formula.

But asides from that the function is doing what you asked.

Paulo
0
 
LVL 13

Expert Comment

by:cri
ID: 8091040
If you have a filtered list (Data|Filter|Autofilter), then use the SUBTOTAL function #9. Otherwise the function above must be adapted. Please state whether the ranges G2 and I2:K2 contains numbers.
0
 

Author Comment

by:Grubeaters
ID: 8105564
The function that I posted will sum only visible columns or rows if they are consecutive.  For example, =sumvisible(A1:D1) sums cells A1, B1, C1 and D1 only if not hidden.  I want to have the function work on non-consecutive columns or rows so for example, =sumvisible(A1, D1, F1, Z1) would sum cells A1, D1, F1 and Z1 only if if they are not hidden.  

I forgot to add the appilaction.volatile so the function looks like this and the F9 key works:

Public Function SumVisible(a As Range) As Double
  Dim c As Range
  Dim value As Double
  Application.Volatile
  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

---------------------

To answer the other comment about G2 and I2:K2 do contain numbers and/or blanks.

0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 9

Accepted Solution

by:
pauloaguia earned 200 total points
ID: 8142193
Sorry for the delay.

And sorry for not having tested the formula properly in my first comment. The thing is that if you use the , (which is the union range operator) Excel thinks it's a new argument to the function. The way to deal with this is letting Excel think that this function can take multiple arguments. But we don't really know how many. So here's a first try on this:

Public Function SumVisible(ParamArray a() As Variant) As Double
 Dim rng As Variant
 Dim value As Double
 Application.Volatile
 value = 0
 
 For Each rng In a
    If Not TypeName(rng) = "Range" Then
        value = ""
        Exit For
    End If
    For Each c In rng.Cells
        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
 Next
 SumVisible = value
End Function

As you can see this function now gets an array of arguments. Which means that it can have a variable number of arguments. Then each one is tested for being a Range and then the sum is calculated for each one as it was when you sent it only one Range.

I haven't found any bugs in it. You can even pick up the ranges by using the formula wizard and selecting them in the Worksheet. Just one thing though: If your list separator is defined in your regional settings as the ; and not the , then you'll need to separate multiple ranges with ; instead of ,

So you can either use it like

=Sumvisible(A1:C1;E1:H1)
Or
=Sumvisible(A1:C1,E1:H1)

depending on your regional settings.

Hope this helps

Paulo
0
 
LVL 13

Expert Comment

by:cri
ID: 8142299
pauloaguia, that is truly neat. Did not find this trick in the net, yet.

BTW, this hit came up, is only closely related, but could be useful: http://www.cof.orst.edu/net/software/excel/tips/#SelectVisible
0
 

Author Comment

by:Grubeaters
ID: 8169030
Thank you.  Excellent!  It works great.
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8169152
Thansk for the points. This is one of those questions I enjoyed because I learnt something from it...
0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8186274
More on this:

Your original function was OK after all. Just found out today that all you needed was to use it like so:

=SUMVisible((A1:C1;F1:I1))
OR
=SUMVisible((A1:C1,F1:I1))

(depending on regional settings)

Just imagine. If I'd known this in the first place we'd never have found out about that ParamArray thing... :)
And now we've learned that you can use () to define combined ranges.... neat :)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 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