Sum visible cells only

[DianeG]

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

 

[Bob Phillips]

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(D27)),D27)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[DianeG]

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]

I think so.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pete_UK]

Have you tried:

=SUBTOTAL(109,range)

?

Hope this helps.

Pete

 

[Bob Phillips]

It is hidden columns Pete.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Have you tried:

=SUBTOTAL(109,range)

?

Hope this helps.

Pete

 

[Pete_UK]

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

 

[Bob Phillips]

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)



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

 

[DianeG]

thanks guys

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)



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