| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding a cell or column to be included in a sum formula
Cell A1 is my "Total" column, with the formula of =sum(B1:X1). When I try to
add a new cell/column to the left of B1 (which then becomes B1, and B1 becomes C1, etc.), the formula does not include the new B1, but instead changes to =sum(C1:X1). I have tried using =sum($B1:X1) but that didn't work. I'd appreciate any insight as to how I can add a new cell/column to the left of the existing first cell/column (i.e., B1) and have the formula adjust to include the new cell/column. I hope that makes sense. Thank you in advance! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding a cell or column to be included in a sum formula
When you insert a new column it should change to
=SUM(C1:Y1) and not Sum(C1:X1) as you have mentioned. If you want to sum the range B1:X1 even after inserting a new column (thus leaving out the old X1 from the sum) use =SUM(INDIRECT("B1:X1")) I am unable to think of a solution to get =SUM(B1:Y1) "lindan8r" wrote: Cell A1 is my "Total" column, with the formula of =sum(B1:X1). When I try to add a new cell/column to the left of B1 (which then becomes B1, and B1 becomes C1, etc.), the formula does not include the new B1, but instead changes to =sum(C1:X1). I have tried using =sum($B1:X1) but that didn't work. I'd appreciate any insight as to how I can add a new cell/column to the left of the existing first cell/column (i.e., B1) and have the formula adjust to include the new cell/column. I hope that makes sense. Thank you in advance! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding a cell or column to be included in a sum formula
You need to cover an additional, leftmost col (left blank) in the SUM
Use instead in A1: =SUM(B1:Y1) where col B is to be left blank Then when you insert, use col C as the insertpoint and the formula in A1 will adjust automatically to include the new inserted col, eg: =SUM(B1:Z1) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "lindan8r" wrote: Cell A1 is my "Total" column, with the formula of =sum(B1:X1). When I try to add a new cell/column to the left of B1 (which then becomes B1, and B1 becomes C1, etc.), the formula does not include the new B1, but instead changes to =sum(C1:X1). I have tried using =sum($B1:X1) but that didn't work. I'd appreciate any insight as to how I can add a new cell/column to the left of the existing first cell/column (i.e., B1) and have the formula adjust to include the new cell/column. I hope that makes sense. Thank you in advance! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding a cell or column to be included in a sum formula
Thank you VERY much, Max. That worked beautifully! :)
"Max" wrote: You need to cover an additional, leftmost col (left blank) in the SUM Use instead in A1: =SUM(B1:Y1) where col B is to be left blank Then when you insert, use col C as the insertpoint and the formula in A1 will adjust automatically to include the new inserted col, eg: =SUM(B1:Z1) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "lindan8r" wrote: Cell A1 is my "Total" column, with the formula of =sum(B1:X1). When I try to add a new cell/column to the left of B1 (which then becomes B1, and B1 becomes C1, etc.), the formula does not include the new B1, but instead changes to =sum(C1:X1). I have tried using =sum($B1:X1) but that didn't work. I'd appreciate any insight as to how I can add a new cell/column to the left of the existing first cell/column (i.e., B1) and have the formula adjust to include the new cell/column. I hope that makes sense. Thank you in advance! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding a cell or column to be included in a sum formula
Thank you for your help. It wasn't exactly what I was looking for, but I
appreciate your time and advice. :) "Sheeloo" wrote: When you insert a new column it should change to =SUM(C1:Y1) and not Sum(C1:X1) as you have mentioned. If you want to sum the range B1:X1 even after inserting a new column (thus leaving out the old X1 from the sum) use =SUM(INDIRECT("B1:X1")) I am unable to think of a solution to get =SUM(B1:Y1) "lindan8r" wrote: Cell A1 is my "Total" column, with the formula of =sum(B1:X1). When I try to add a new cell/column to the left of B1 (which then becomes B1, and B1 becomes C1, etc.), the formula does not include the new B1, but instead changes to =sum(C1:X1). I have tried using =sum($B1:X1) but that didn't work. I'd appreciate any insight as to how I can add a new cell/column to the left of the existing first cell/column (i.e., B1) and have the formula adjust to include the new cell/column. I hope that makes sense. Thank you in advance! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding a cell or column to be included in a sum formula
Welcome, glad it did.
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "lindan8r" wrote in message ... Thank you VERY much, Max. That worked beautifully! :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to find what number in Column A is not included in Column B? | Excel Discussion (Misc queries) | |||
| Unable to sort when column is included in range | Excel Discussion (Misc queries) | |||
| Help, add subtotals included in one column to get a grand total | Excel Discussion (Misc queries) | |||
| Can a cell in excel contain text and have a formula included? | Excel Discussion (Misc queries) | |||
| prevent column from being included in sort in Excel | Excel Discussion (Misc queries) | |||
Linear Mode
