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

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.

hide them

--
Don Guillett
SalesAid Software

"pjs1121" wrote in message
...
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.

Have you thought of using pivot tables?

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

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


Regards,

Peo Sjoblom



"pjs1121" wrote:

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.

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)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Don Guillett" wrote ...
hide them

"pjs1121" wrote ...
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 include them later.