Problem with SUM function and absolute & relative references

**jjin082693** · 06-25-2012, 11:04 PM

Say I have a table with the following column:

A

1

I'm trying to set up a sum column B that sums all previous corresponding values in A. Example:

A	B
1	1
1	2
1	3

To do this, i'm currently defining the B1 cell as =SUM($A$1:A1). This allows B2 to be defined as =SUM($A$1:A2), B3 as =SUM($A$1:A3), and so on.

At first, this all works out for me. But then, when I try to append another row to the bottom of the table, the function screws up for PRECEDING B-cell, so that, if I enter a 4th row, B4 is =SUM($A$1:A4), but so is B3. Furthermore, this problem continues as I add rows; for example, if I add rows so that the table has a total of 8 rows, B3 thru B8 would all be defined as =SUM($A$1:A8).

Anyone have any advice on why this is happening, and what I can do to solve this problem?

**FDibbins** · 06-25-2012, 11:32 PM

maybe this is too simplistic, or maybe im not understanding you correctly, but couldnt you just add the cell above to the cell at the side?

so B2 would be B1+A2 copied down. then you wouldnt need to absolute anything. you said its a table, so im assuming its what excel defines as a TABLE, in which case it can do funky things with fromulas (from my experience)

**MarvinP** · 06-25-2012, 11:37 PM

Hi and welcome to the forum,

How about trying another formula in B2 = A2+B1 and pull it down.
(In B1 you will need a formula of = A1)

When you insert a new row simply pull the formula in B2 all the way to the end.

Does this work for you?

**vlady** · 06-25-2012, 11:40 PM

with those formulas maybe immediately drag down say up to 20-30 cells to copy the formula then you can add values in column a.

and maybe also put some checking like
=if(A1="","",SUM($A$1:A1)

**the error i think is when you drag it, copies only one cell like you said

B3 thru B8 would all be defined as =SUM($A$1:A8)