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

L

laserhallam

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.
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
T

Tyro

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
 
T

T. Valko

Ron Rosenfeld said:
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
Click to expand...

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.
 
Top