How do I get excel to sum a range without including hidden cells?
 

I need to sum a total dollar amount from a column in a spreadsheet. But I
need to hide certain rows after they are no longer needed. I don't want to
simply delete these as I may need to reference them later. The problem is the
running total I have always includes all the cells, hidden or not. I would
like the running total or SUM to not include the hidden rows. How can this be
done?

How do I get excel to sum a range without including hidden cells?
 

look in the help index for SUBTOTAL

--
Don Guillett
SalesAid Software

"Jet" wrote in message
...
I need to sum a total dollar amount from a column in a spreadsheet. But I
need to hide certain rows after they are no longer needed. I don't want to
simply delete these as I may need to reference them later. The problem is
the
running total I have always includes all the cells, hidden or not. I would
like the running total or SUM to not include the hidden rows. How can this
be
done?

How do I get excel to sum a range without including hidden cells?
 

One caveat - Subtotal in earlier versions of XL doesn't exclude cells that
are hidden by the user, only those that are hidden using a filter. That
functionality was added in XL2002 or 2003 (so I've learned).

If that is the case, you have to use a UDF. Paste the function code below
into a VBA module. To use:
=SUMPRODUCT(--(isvisible(A1:A10)), A1:A10)


Function IsVisible(rngData As Range) As Variant
Dim arrTemp() As Variant
Dim i As Long
Dim t As Long

Application.Volatile True
With rngData
ReDim arrTemp(1 To .Rows.Count, _
1 To .Columns.Count)
For i = 1 To .Rows.Count
For t = 1 To .Columns.Count
arrTemp(i, t) = Not .Cells(i, t).EntireRow.Hidden And _
Not .Cells(i, t).EntireColumn.Hidden
Next t
Next i
End With

IsVisible = arrTemp
End Function


"Jet" wrote:

I need to sum a total dollar amount from a column in a spreadsheet. But I
need to hide certain rows after they are no longer needed. I don't want to
simply delete these as I may need to reference them later. The problem is the
running total I have always includes all the cells, hidden or not. I would
like the running total or SUM to not include the hidden rows. How can this be
done?