| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum visible cells only
Hi
Is there a way to sum visible cells only without using a pivot table? Some values are sitting in hidden columns and they still add up, I'd like them to be ignored Thanks Diane |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum visible cells only
Add this UDF
'-------------------------------------------------------------------------- Function IsVisible(ByVal rng As Range) '-------------------------------------------------------------------------- Dim oRange As Range Dim i As Long Dim ary() If rng.Rows.Count 1 And _ rng.Columns.Count 1 Then IsVisible = CVErr(xlErrRef) Else If rng.Rows.Count rng.Columns.Count Then ReDim ary(1 To 1, 1 To rng.Rows.Count) For Each oRange In rng.Rows i = i + 1 ary(1, i) = Not oRange.EntireRow.Hidden Next oRange IsVisible = Application.Transpose(ary) Else ReDim ary(1 To 1, 1 To rng.Columns.Count) For Each oRange In rng.Columns i = i + 1 ary(1, i) = Not oRange.EntireColumn.Hidden Next oRange IsVisible = ary End If End If End Function and use like so =SUMPRODUCT(--(isvisible(D2:D7)),D2:D7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DianeG" wrote in message ... Hi Is there a way to sum visible cells only without using a pivot table? Some values are sitting in hidden columns and they still add up, I'd like them to be ignored Thanks Diane |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum visible cells only
Thanks very much Bob, do you know if is this the only way you can do it? I
know the guy who needs it doesn't know VBA Regards Diane "Bob Phillips" wrote: Add this UDF '-------------------------------------------------------------------------- Function IsVisible(ByVal rng As Range) '-------------------------------------------------------------------------- Dim oRange As Range Dim i As Long Dim ary() If rng.Rows.Count 1 And _ rng.Columns.Count 1 Then IsVisible = CVErr(xlErrRef) Else If rng.Rows.Count rng.Columns.Count Then ReDim ary(1 To 1, 1 To rng.Rows.Count) For Each oRange In rng.Rows i = i + 1 ary(1, i) = Not oRange.EntireRow.Hidden Next oRange IsVisible = Application.Transpose(ary) Else ReDim ary(1 To 1, 1 To rng.Columns.Count) For Each oRange In rng.Columns i = i + 1 ary(1, i) = Not oRange.EntireColumn.Hidden Next oRange IsVisible = ary End If End If End Function and use like so =SUMPRODUCT(--(isvisible(D2:D7)),D2:D7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DianeG" wrote in message ... Hi Is there a way to sum visible cells only without using a pivot table? Some values are sitting in hidden columns and they still add up, I'd like them to be ignored Thanks Diane |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum visible cells only
I think so.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DianeG" wrote in message ... Thanks very much Bob, do you know if is this the only way you can do it? I know the guy who needs it doesn't know VBA Regards Diane "Bob Phillips" wrote: Add this UDF '-------------------------------------------------------------------------- Function IsVisible(ByVal rng As Range) '-------------------------------------------------------------------------- Dim oRange As Range Dim i As Long Dim ary() If rng.Rows.Count 1 And _ rng.Columns.Count 1 Then IsVisible = CVErr(xlErrRef) Else If rng.Rows.Count rng.Columns.Count Then ReDim ary(1 To 1, 1 To rng.Rows.Count) For Each oRange In rng.Rows i = i + 1 ary(1, i) = Not oRange.EntireRow.Hidden Next oRange IsVisible = Application.Transpose(ary) Else ReDim ary(1 To 1, 1 To rng.Columns.Count) For Each oRange In rng.Columns i = i + 1 ary(1, i) = Not oRange.EntireColumn.Hidden Next oRange IsVisible = ary End If End If End Function and use like so =SUMPRODUCT(--(isvisible(D2:D7)),D2:D7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DianeG" wrote in message ... Hi Is there a way to sum visible cells only without using a pivot table? Some values are sitting in hidden columns and they still add up, I'd like them to be ignored Thanks Diane |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum visible cells only
Have you tried:
=SUBTOTAL(109,range) ? Hope this helps. Pete On Mar 5, 7:00*pm, DianeG wrote: Thanks very much Bob, do you know if is this the only way you can do it? *I know the guy who needs it doesn't know VBA Regards Diane "Bob Phillips" wrote: Add this UDF '-------------------------------------------------------------------------- Function IsVisible(ByVal rng As Range) '-------------------------------------------------------------------------- Dim oRange As Range Dim i As Long Dim ary() * * If rng.Rows.Count 1 And _ * * * * rng.Columns.Count 1 Then * * * * IsVisible = CVErr(xlErrRef) * * Else * * * * If rng.Rows.Count rng.Columns.Count Then * * * * * * ReDim ary(1 To 1, 1 To rng.Rows.Count) * * * * * * For Each oRange In rng.Rows * * * * * * * * i = i + 1 * * * * * * * * ary(1, i) = Not oRange.EntireRow.Hidden * * * * * * Next oRange * * * * * * IsVisible = Application.Transpose(ary) * * * * Else * * * * * * ReDim ary(1 To 1, 1 To rng.Columns.Count) * * * * * * For Each oRange In rng.Columns * * * * * * * * i = i + 1 * * * * * * * * ary(1, i) = Not oRange.EntireColumn.Hidden * * * * * * Next oRange * * * * * * IsVisible = ary * * * * End If * * End If End Function and use like so =SUMPRODUCT(--(isvisible(D2:D7)),D2:D7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DianeG" wrote in message ... Hi Is there a way to sum visible cells only without using a pivot table? Some values are sitting in hidden columns and they still add up, I'd like them to be ignored Thanks Diane- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum visible cells only
It is hidden columns Pete.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pete_UK" wrote in message ... Have you tried: =SUBTOTAL(109,range) ? Hope this helps. Pete On Mar 5, 7:00 pm, DianeG wrote: Thanks very much Bob, do you know if is this the only way you can do it? I know the guy who needs it doesn't know VBA Regards Diane "Bob Phillips" wrote: Add this UDF '-------------------------------------------------------------------------- Function IsVisible(ByVal rng As Range) '-------------------------------------------------------------------------- Dim oRange As Range Dim i As Long Dim ary() If rng.Rows.Count 1 And _ rng.Columns.Count 1 Then IsVisible = CVErr(xlErrRef) Else If rng.Rows.Count rng.Columns.Count Then ReDim ary(1 To 1, 1 To rng.Rows.Count) For Each oRange In rng.Rows i = i + 1 ary(1, i) = Not oRange.EntireRow.Hidden Next oRange IsVisible = Application.Transpose(ary) Else ReDim ary(1 To 1, 1 To rng.Columns.Count) For Each oRange In rng.Columns i = i + 1 ary(1, i) = Not oRange.EntireColumn.Hidden Next oRange IsVisible = ary End If End If End Function and use like so =SUMPRODUCT(--(isvisible(D2:D7)),D2:D7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DianeG" wrote in message ... Hi Is there a way to sum visible cells only without using a pivot table? Some values are sitting in hidden columns and they still add up, I'd like them to be ignored Thanks Diane- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum visible cells only
Yes, I realised that after re-reading the post - I should pay more
attention !! <bg It does seem to crop up fairly often, though, so perhaps Microsoft may get SUBTOTAL to work in this way for columns as well as rows some time in the future (after pigs learn to fly !!) Pete On Mar 5, 10:36*pm, "Bob Phillips" wrote: It is hidden columns Pete. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pete_UK" wrote in message ... Have you tried: =SUBTOTAL(109,range) ? Hope this helps. Pete On Mar 5, 7:00 pm, DianeG wrote: Thanks very much Bob, do you know if is this the only way you can do it? I know the guy who needs it doesn't know VBA Regards Diane "Bob Phillips" wrote: Add this UDF '-------------------------------------------------------------------------- Function IsVisible(ByVal rng As Range) '-------------------------------------------------------------------------- Dim oRange As Range Dim i As Long Dim ary() If rng.Rows.Count 1 And _ rng.Columns.Count 1 Then IsVisible = CVErr(xlErrRef) Else If rng.Rows.Count rng.Columns.Count Then ReDim ary(1 To 1, 1 To rng.Rows.Count) For Each oRange In rng.Rows i = i + 1 ary(1, i) = Not oRange.EntireRow.Hidden Next oRange IsVisible = Application.Transpose(ary) Else ReDim ary(1 To 1, 1 To rng.Columns.Count) For Each oRange In rng.Columns i = i + 1 ary(1, i) = Not oRange.EntireColumn.Hidden Next oRange IsVisible = ary End If End If End Function and use like so =SUMPRODUCT(--(isvisible(D2:D7)),D2:D7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DianeG" wrote in message ... Hi Is there a way to sum visible cells only without using a pivot table? Some values are sitting in hidden columns and they still add up, I'd like them to be ignored Thanks Diane- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum visible cells only
There seems to be an aversion to making good facilities even better, and
SUBTOTAL is a goodie IMO, too much emphasis on visual appeal (or not). -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pete_UK" wrote in message ... Yes, I realised that after re-reading the post - I should pay more attention !! <bg It does seem to crop up fairly often, though, so perhaps Microsoft may get SUBTOTAL to work in this way for columns as well as rows some time in the future (after pigs learn to fly !!) Pete On Mar 5, 10:36 pm, "Bob Phillips" wrote: It is hidden columns Pete. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pete_UK" wrote in message ... Have you tried: =SUBTOTAL(109,range) ? Hope this helps. Pete On Mar 5, 7:00 pm, DianeG wrote: Thanks very much Bob, do you know if is this the only way you can do it? I know the guy who needs it doesn't know VBA Regards Diane "Bob Phillips" wrote: Add this UDF '-------------------------------------------------------------------------- Function IsVisible(ByVal rng As Range) '-------------------------------------------------------------------------- Dim oRange As Range Dim i As Long Dim ary() If rng.Rows.Count 1 And _ rng.Columns.Count 1 Then IsVisible = CVErr(xlErrRef) Else If rng.Rows.Count rng.Columns.Count Then ReDim ary(1 To 1, 1 To rng.Rows.Count) For Each oRange In rng.Rows i = i + 1 ary(1, i) = Not oRange.EntireRow.Hidden Next oRange IsVisible = Application.Transpose(ary) Else ReDim ary(1 To 1, 1 To rng.Columns.Count) For Each oRange In rng.Columns i = i + 1 ary(1, i) = Not oRange.EntireColumn.Hidden Next oRange IsVisible = ary End If End If End Function and use like so =SUMPRODUCT(--(isvisible(D2:D7)),D2:D7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DianeG" wrote in message ... Hi Is there a way to sum visible cells only without using a pivot table? Some values are sitting in hidden columns and they still add up, I'd like them to be ignored Thanks Diane- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum visible cells only
thanks guys
"Bob Phillips" wrote: There seems to be an aversion to making good facilities even better, and SUBTOTAL is a goodie IMO, too much emphasis on visual appeal (or not). -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pete_UK" wrote in message ... Yes, I realised that after re-reading the post - I should pay more attention !! <bg It does seem to crop up fairly often, though, so perhaps Microsoft may get SUBTOTAL to work in this way for columns as well as rows some time in the future (after pigs learn to fly !!) Pete On Mar 5, 10:36 pm, "Bob Phillips" wrote: It is hidden columns Pete. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pete_UK" wrote in message ... Have you tried: =SUBTOTAL(109,range) ? Hope this helps. Pete On Mar 5, 7:00 pm, DianeG wrote: Thanks very much Bob, do you know if is this the only way you can do it? I know the guy who needs it doesn't know VBA Regards Diane "Bob Phillips" wrote: Add this UDF '-------------------------------------------------------------------------- Function IsVisible(ByVal rng As Range) '-------------------------------------------------------------------------- Dim oRange As Range Dim i As Long Dim ary() If rng.Rows.Count 1 And _ rng.Columns.Count 1 Then IsVisible = CVErr(xlErrRef) Else If rng.Rows.Count rng.Columns.Count Then ReDim ary(1 To 1, 1 To rng.Rows.Count) For Each oRange In rng.Rows i = i + 1 ary(1, i) = Not oRange.EntireRow.Hidden Next oRange IsVisible = Application.Transpose(ary) Else ReDim ary(1 To 1, 1 To rng.Columns.Count) For Each oRange In rng.Columns i = i + 1 ary(1, i) = Not oRange.EntireColumn.Hidden Next oRange IsVisible = ary End If End If End Function and use like so =SUMPRODUCT(--(isvisible(D2:D7)),D2:D7) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DianeG" wrote in message ... Hi Is there a way to sum visible cells only without using a pivot table? Some values are sitting in hidden columns and they still add up, I'd like them to be ignored Thanks Diane- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum Visible Cells | Excel Worksheet Functions | |||
| Sum of visible cells only | Excel Worksheet Functions | |||
| sum only visible cells | Excel Worksheet Functions | |||
| Visible Cells Only! | Excel Worksheet Functions | |||
| can i sum up only visible cells? | Excel Worksheet Functions | |||
Linear Mode
