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