Sum visible cells only

  • Thread starter Thread starter DianeG
  • Start date Start date
D

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
 
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)
 
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
 
I think so.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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
 
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
 
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
 
thanks guys

Bob Phillips said:
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
Click to expand...
 
Back
Top