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 -