Sum Function Without Including Hidden Rows?

[FredL]

I'm using Excel 2000 and have *hidden* several rows that include numeric
data. I want to do a sum function on some of the columns intersecting
those rows, but when I insert an AutoSum function, it includes data in
the visible rows AND the hidden rows. Is there an easy way to perform
a sum function that *only* includes the rows that are *-visible?-*
(Which rows are hidden/visible are subject to constant change)

Thanks for your help!

 

[Peo Sjoblom]

You would need a UDF for that or if there is a pattern (like if every other
column/row is hidden)
that you can use regular functions but we need to know where the data starts
and which columns/rows are hidden

Here's a UDF by Dave Petersson

Option Explicit
Function SumVisible(myRng As Range) As Double
Application.Volatile

Dim myCell As Range
Dim mySum As Double

For Each myCell In myRng.Cells
If myCell.EntireRow.Hidden = True Then
'don't add it
Else
If IsNumeric(myCell.Value) Then
mySum = mySum + myCell.Value
End If
End If
Next myCell

SumVisible = mySum

End Function

Use it like =SUMVISIBLE(A1:A100)

how to install macros and UDFs

http://www.mvps.org/dmcritchie/excel/formula.htm#install

 

[David McRitchie]

Hi Fred,
Read about SUBTOTAL Worksheet Function in Excel HELP, Index, ...

Post back to this thread if not clear to you.

 

[Don Guillett]

Sub subvis()
MsgBox Application.Sum(Columns("a").SpecialCells(xlVisible))
End Sub