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 -