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
|