Excel: Excluding Hidden Rows in Formulas 1

[superco7]

I am running Office 2000 and was wondering if anyone knew how to go about excluding hidden rows when adding a range of columns values together? Aside from just individually selecting the visible cells. Thanks.

 

[KenWright]

As long as the rows have been hidden using a filter such as Autofilter, you can use the SUBTOTAL function to do this. In Excel 2003 you can exytend that to hidden rows via normal hiding methods as well.

=SUBTOTAL(9,A1:A1000)

9 is the SUM argument

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SColbert]

I use the following code that takes care of this (& works better for me as I cannot always use AutoFilters in my spreadsheets):

Function SumVisibleCells(CellsToSum As Object)
'sums only visible cells, ignoring cells in hidden rows or columns

Dim cell As Variant
Dim total As Single 'I use Single as my values contain decimals

Application.Volatile
For Each cell In CellsToSum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
SumVisibleCells = total

End Function

To check type the following data into a blank spreadsheet:
A1 = 1 B1 = 2
A2 = 2 B2 = 4
A3 = 3 B3 = 6
A4 = 4 B4 = 8
A5 = 5 B5 = 10

Hide row 3

In A6 type the following formula:
=SUM(A1:B5)
result should be 45

In B6 type the following formula:
=sumvisiblecells(A1:B5)
result should be 36

Remember, user must press F9 after spreadsheet changes to recalculate

 

[superco7]

Thanks Ken and SColbert, I will have to give those a try.