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 -