Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
copy group of cells to another group of cells using "IF" in third | Excel Worksheet Functions |