How do I add up column of numbers without including hidden rows?
 

I am trying to add up a column of numbers but I have hidden rows in the
spreadsheet and don't want to include them in the sum.

How do I add up column of numbers without including hidden rows?
 

On Thu, 31 Jan 2008 20:33:01 -0800, laserhallam
wrote:

I am trying to add up a column of numbers but I have hidden rows in the
spreadsheet and don't want to include them in the sum.

How were the rows hidden?

What version of Excel?

In Excel 2003 (and I assume later), you can use the function:

=SUBTOTAL(109,rng) where range is the reference to the cells. Hidden rows
will not be included in the sum.

For earlier versions of Excel, SUBTOTAL(9, rng) will not include rows that were
hidden as the result of a filter. (I'm really not sure about Excel 2002,
though).
--ron

How do I add up column of numbers without including hidden rows?
 

If you hide rows manually, the SUBTOTAL function ignores any values hidden
by a filter. The SUBTOTAL function with function_num of 1 to 11 includes
rows hidden manually with hide rows. The SUBTOTAL function with function_num
of 101-111 excludes rows hidden manually with hide rows. How do I know this?
I read the help file. :)

Tyro

"laserhallam" wrote in message
...
I am trying to add up a column of numbers but I have hidden rows in the
spreadsheet and don't want to include them in the sum.

How do I add up column of numbers without including hidden rows?
 

"Ron Rosenfeld" wrote in message
...
On Thu, 31 Jan 2008 20:33:01 -0800, laserhallam
wrote:

I am trying to add up a column of numbers but I have hidden rows in the
spreadsheet and don't want to include them in the sum.

How were the rows hidden?

What version of Excel?

In Excel 2003 (and I assume later), you can use the function:

=SUBTOTAL(109,rng) where range is the reference to the cells. Hidden
rows
will not be included in the sum.

For earlier versions of Excel, SUBTOTAL(9, rng) will not include rows that
were
hidden as the result of a filter. (I'm really not sure about Excel 2002,
though).
--ron

In Excel 2002 and earlier, SUBTOTAL(n,rng) will only exclude those rows that
are hidden by using a filter.

The 100 series arguments were added in Excel 2003 and are still available in
Excel 2007.

--
Biff
Microsoft Excel MVP