Sum function which ignores hidden rows
 

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.


--
bucketheaduk
------------------------------------------------------------------------
bucketheaduk's Profile: http://www.excelforum.com/member.php...o&userid=28032
View this thread: http://www.excelforum.com/showthread...hreadid=475352

On Wed, 12 Oct 2005 06:02:39 -0500, bucketheaduk
wrote:


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

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"
wrote in message
news:bucketheaduk.1wsd6f_1129115114.5725@excelforu m-nospam.com...

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.


--
bucketheaduk
------------------------------------------------------------------------
bucketheaduk's Profile:
http://www.excelforum.com/member.php...o&userid=28032
View this thread: http://www.excelforum.com/showthread...hreadid=475352