How do I "turn off" cells so they are not included in autosum

[Guest]

I'm working with budgets and would like to be able to remove line items so
that they are not included in subtotals and totals lines, yet keep the line
items in place on the budget so that we can turn them back "on" if we need to
inlcude them later.

 

[Don Guillett]

hide them

 

[Guest]

Have you thought of using pivot tables?

http://peltiertech.com/Excel/Pivots/pivotstart.htm

http://www.cpearson.com/excel/pivots.htm


Regards,

Peo Sjoblom

 

[David McRitchie]

Hi ...,
If you hide the rows with a filter, you would need to use
=SUBTOTAL(9, ...)

If you use a helper column you can use an indication of an "x" to
omit from the total:
G11: =SUMIF(G2:G10,"<>x",F2:F10)
so that you can insert rows immediately above your total, best written as
G11: =SUMIF(G$2:OFFSET(G11,-1,0),"<>x">F$2:OFFSET(F11,-1,0))

or use the indication to include in the total
=SUMIF(G2:G10,"x",F2:F10)
so that you can insert rows immediately above your total, best written as
G11: =SUMIF(G$2:OFFSET(G11,-1,0),"x">F$2:OFFSET(F11,-1,0))

You might include both totals to make things clearer, in which case
the offset would be changed depending on where to place the
omitted items total. OFFSET(location of this total, adjust row, adjust column)