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.

On Wed, 12 Oct 2005 06:02:39 -0500, bucketheaduk
<[email protected]> 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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>