Do not calculate hidden cells in Excel

[canadrian]

Hello,

I haven't been able to find out 100% if this is possible in Excel 2000. I have a user with a list of about 1000 rows. 400 or so of those rows have been hidden manually, and are spread sporatically through the document. He would like to get the sum of a given column, while disregarding the hidden data from the total. SUBTOTAL(9,RANGE) doesn't work in this instance.

I know there are ways around this by using Go to: and pasting into a separate worksheet, etc. However, are there any formulas to get the sum/average/etc of a range while disregarding hidden cells?

 

[onedtent]

with xl2K u need a UDF. this one is from David McRitchie:

Function SumVisible(Rg As Range) As Double
Dim Cell As Range
Application.Volatile
For Each Cell In Rg
If Not (cell.Rows.Hidden Or cell.Columns.Hidden) Then
If IsNumeric(Cell.Value) Then
SumVisible = SumVisible + Cell.Value
End If
End If
Next
End Function


with XL2003, u can use SUBTOTAL(109,range)


- onedtent Onedtent OnedTent OnedTenT OneDTenT
If a particular post has been answered, I will most likely not add to the reply

 

[AnotherHiggins]

-> 400 or so of those rows have been hidden manually

Yuck.

Is there any criteria by which he is deciding which rows to hide? Is he aware of Data > Filter > AutoFilter?

Surely there's a better way for him to proceed than manually sifting through 1000 rows and manually deciding which rows shouldn't be counted. No matter how diligent this user is, everyone makes mistakes, and that process all but guarantees that he'll return inaccurate information sooner or later.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[canadrian]

onedtent - Thank you for providing me with this user-defined function. I have tested it and passed it on to my user.

anotherhiggins - I couldn't agree more. I have made the suggestion a couple of times, but didn't get much in the way of a response. Oh well; you know how it is sometimes.

Thank you both very much for your prompt replies!