Summing only those cells that aren't hidden
 

Is there a way to add together only those cells that aren't hidden? When
trying to do the sum function, it seems to add in the hidden cells.

Use the SUBTOTAL function...

=SUBTOTAL(9,Range)

Hope this helps!

In article ,
"JodyK" wrote:

Is there a way to add together only those cells that aren't hidden? When
trying to do the sum function, it seems to add in the hidden cells.

Use the "subtotal" function instead of sum. It has 2 parameters for input: a
function # and the range to be considered. The function # is used to give
count, average, sum, and other functions, and it works on all cells unless
you add 100 to it. The function for summing is 9, but if you use 109 it will
ignore hidden cells. Note that this function does not do well summing
horizontally, and will not ignore hidden columns if you use it that way.
When used on a vertical range it will ignore the hidden rows. See Excel help
for the subtotal function for more info.

Good luck,
AC
"JodyK" wrote in message
...
Is there a way to add together only those cells that aren't hidden? When
trying to do the sum function, it seems to add in the hidden cells.

This depends on what you mean by "hidden". SUBTOTAL ignores rows that are
hidden by filtering, but NOT rows that are hidden manually.


On Fri, 9 Sep 2005 17:09:42 -0500, "Alan Cannon"
wrote:

Use the "subtotal" function instead of sum. It has 2 parameters for input: a
function # and the range to be considered. The function # is used to give
count, average, sum, and other functions, and it works on all cells unless
you add 100 to it. The function for summing is 9, but if you use 109 it will
ignore hidden cells. Note that this function does not do well summing
horizontally, and will not ignore hidden columns if you use it that way.
When used on a vertical range it will ignore the hidden rows. See Excel help
for the subtotal function for more info.

Good luck,
AC
"JodyK" wrote in message
...
Is there a way to add together only those cells that aren't hidden? When
trying to do the sum function, it seems to add in the hidden cells.

Thanks for this information. I've passed it along to the person who needed
the help.

"Myrna Larson" wrote:

This depends on what you mean by "hidden". SUBTOTAL ignores rows that are
hidden by filtering, but NOT rows that are hidden manually.


On Fri, 9 Sep 2005 17:09:42 -0500, "Alan Cannon"
wrote:

Use the "subtotal" function instead of sum. It has 2 parameters for input: a
function # and the range to be considered. The function # is used to give
count, average, sum, and other functions, and it works on all cells unless
you add 100 to it. The function for summing is 9, but if you use 109 it will
ignore hidden cells. Note that this function does not do well summing
horizontally, and will not ignore hidden columns if you use it that way.
When used on a vertical range it will ignore the hidden rows. See Excel help
for the subtotal function for more info.

Good luck,
AC
"JodyK" wrote in message
...
Is there a way to add together only those cells that aren't hidden? When
trying to do the sum function, it seems to add in the hidden cells.

Myrna

A head's up.

A new feature in Excel 2003 is the addition of a double-duty SUBTOTAL function
which ignores rows hidden manually.

=SUBTOTAL(9,A1:A10) ignores rows hidden by filtering.

=SUBTOTAL(109,A1:A10) also ignores rows hidden manually.


Gord Dibben Excel MVP

On Tue, 13 Sep 2005 07:46:23 -0700, "JodyK"
wrote:

Thanks for this information. I've passed it along to the person who needed
the help.

"Myrna Larson" wrote:

This depends on what you mean by "hidden". SUBTOTAL ignores rows that are
hidden by filtering, but NOT rows that are hidden manually.


On Fri, 9 Sep 2005 17:09:42 -0500, "Alan Cannon"
wrote:

Use the "subtotal" function instead of sum. It has 2 parameters for input: a
function # and the range to be considered. The function # is used to give
count, average, sum, and other functions, and it works on all cells unless
you add 100 to it. The function for summing is 9, but if you use 109 it will
ignore hidden cells. Note that this function does not do well summing
horizontally, and will not ignore hidden columns if you use it that way.
When used on a vertical range it will ignore the hidden rows. See Excel help
for the subtotal function for more info.

Good luck,
AC
"JodyK" wrote in message
...
Is there a way to add together only those cells that aren't hidden? When
trying to do the sum function, it seems to add in the hidden cells.