Summing only those cells that aren't hidden

[Guest]

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!

 

[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

 

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

 

[Guest]

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

 

[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