Summing only those cells that aren't hidden

[JodyK]

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.

[Domenic]

Use the SUBTOTAL function...

=SUBTOTAL(9,Range)

Hope this helps!

In article <[email protected]>,
"JodyK" <[email protected]> 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.

[Alan Cannon]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 Larson]

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" <[email protected]>
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" <[email protected]> wrote in message
>news:[email protected]...
>> 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.
>

[JodyK]

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" <[email protected]>
> 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" <[email protected]> wrote in message
> >news:[email protected]...
> >> 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.
> >
>

[Gord Dibben]

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" <[email protected]>
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" <[email protected]>
>> 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" <[email protected]> wrote in message
>> >news:[email protected]...
>> >> 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.
>> >
>>