How do I use auto sum with hidden cells?

[Guest]

I am trying to use the auto sum feature for a column of cells. I have quite
a few of hidden cells that I do not want to be factored into my total. How
do I get it to do the auto sum so it doesn't include those hidden cells?

 

[Arvi Laanemets]

Hi

When you did use autofilter feature to hide rows, then you can use SUBTOTAL
function to return sum, count, etc. of all visible cells.

When you simply did set the height of rows to 0, then the only option is to
have a (hidden) column, where you store some value for hidden rows and
another one for visible rows - and then you can use SUMIF to summarize
visible values in some another column.


Arvi Laanemets

 

[Debra Dalgleish]

You can use the SUBTOTAL function to sum the visible cells, if the rows
were hidden by using Data>Filter>Autofilter. For example:

=SUBTOTAL(9,C3:C12)

In Excel 2003, you can also use SUBTOTAL to sum visible cells, if rows
were manually hidden. For example:

=SUBTOTAL(109,C3:C12)