problem with sum function

[enxocoebl]

hey
i have this tiny little problem. i have no idea why this is happening and what i can do to make it alright.
formula in j4 to j20 is =IF(P2="",J4,H1+I1)...( as i go down to j5.. P2 becomes P3 and j4 becomes j5 and so on)
now in cell j21 i have a sum function (=SUM(J4:J20))
sum function does not work.. it remains zero all the time
i selected (J4:J20) in the formula bar and hit F9. this is what i got.
{45;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
even when the first cell is 45 the sum cell (j21) shows 0.
how can i make it work.
thank you.

 

[pplstuff]

Are there any "" in j4:j20? It might be reading it as "text" and not summing the column as a result.

 

[enxocoebl]

No blanks in the cells between j4 and j20
j4 has 45 (as a result of the addition of 44 and 1 in cells h1 and i1). the rest are 0's

 

[Colin Legg]

It's because your formulas in the J column have circular references (they refer to themselves).
eg. In J4 your formula is

=IF(P2="",J4,H1+I1)


Perhaps you want this instead?

=IF(P2="",0,H1+I1)

 

[pplstuff]

If possible, try to enter the same formulas in a different area of the workbook. If it works in the new area, delete the columns where the current formulas are, and reenter them. Hopefully, this doesn't cause too much trouble.

 

[enxocoebl]

pplstuff you maybe right. i tried entering values in all the cells and it worked. cell j21 showed the sum of j4:j20
colin legg: i need that because i want it to show the last value in the cell before it is updated.

when all the cells in column P (P2,P3,P4...) are not empty then i get the sum.

 

[enxocoebl]

but i want it to calculate the sum as i fill in the values.. like how SUM function is supposed to work.
also please tell me what i can do if there are "" in between.