Sum Function

E

Edge118

Hi,
In my spreadsheet I have data in B1:K1 and in L1 I have the sum o
those rows with the formula =SUM(B1:K1). The problem I have is if
insert a column the SUM function doesn't change to accomodate this. I
there any way to have the SUM include the new columns that I ad
automatically? Thanks.

R
 
R

Rutgers_Excels

You need to insert your new rows above the cell that calculates the sum
If you are trying to insert the row in L1, it will not be included i
the sum formula. However, if you insert it above L1 (anywhere from B
to K1) it will be included in your sum formula.

I would leave L1 blank. In M1, insert sum(B1:L1). If you insert ne
rows, make sure that you insert them in the blank cell above you sum
i.e. L1
 
D

dnickelson

If you add columns inside the sum range (after B or before K), th
formula should adjust. The formula will not adjust to columns inserte
before B, for example. If this is not how it is performing, could yo
give a specific example
 
S

Sandy Mann

RB

=SUM(INDIRECT("B1:"& CHAR(COLUMN(OFFSET(L1,0,-1))+64) & "1"))

Works for me when I sit on column L and insert another column.

HTH

Sandy
 
D

Dave Peterson

Another way:

=SUM(B1:OFFSET(L1,0,-1))



Edge118 < said:
Hi,
In my spreadsheet I have data in B1:K1 and in L1 I have the sum of
those rows with the formula =SUM(B1:K1). The problem I have is if I
insert a column the SUM function doesn't change to accomodate this. Is
there any way to have the SUM include the new columns that I add
automatically? Thanks.

RB
Click to expand...
 
Top