Sum function which ignores hidden rows

[bucketheaduk]

Hey

I'm looking for a way to sum rows but ignore the adjacent rows whic
are hidden. excel is adding the stuff in the unhide rows (which i
what i want) but also adding the hidden row numbers too (which i don'
want).

cheers for the help

 

[Ron Rosenfeld]

Hey

I'm looking for a way to sum rows but ignore the adjacent rows which
are hidden. excel is adding the stuff in the unhide rows (which is
what i want) but also adding the hidden row numbers too (which i don't
want).

cheers for the help.

If the rows are hidden as a result of the list being filtered, you could use
the SUBTOTAL(9,rng) function.

If the rows ae hidden by some other method, then you can use a UDF (user
defined function) written in VBA.

This is from support.microsoft.com
====================
Function Sum_Visible_Cells(Cells_To_Sum As Object)
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
total = total + cell.Value
End If
End If
Next
Sum_Visible_Cells = total
End Function
========================

To enter this, <alt><F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code into the window that opens.

Return to your worksheet and enter the formula:

=Sum_Visible_cells(cell_ref) into some cell.



--ron

 

[Bob Phillips]

Look at the =SUBTOTAL() function
In XL 2002 and before, it only ignores rows hidden by a filter, but XL 2003
gives the option to ignore manually hidden rows.


--
HTH

Bob Phillips

"bucketheaduk" <[email protected]>
wrote in message
news:[email protected]...