sum above

[Tapp]

HI

Id it possible to ge the sum above other that = sum(A1:A20) for example??

My problem is this

I have three columns that are used for calculation
I J K and L
if the word 'total' is typed in column I then column L will total whats above (L1 to L27 or what ever) if not then column L will contain the values of J and K added together

here is the functiom and it works ok

=IF(I2="total",SUM(L1:L2),IF(D2=0,"",J2+K2))

here is whare the snag comes in

When I drag the row down the page applying the formula to other rows the sormula changes as you wuld expect BUT how can I get the Sum bit to do what I want

=IF(I3="total",SUM(L2:L3),IF(D3=0,"",J3+K3))

SO L2 becomes L3 and so on I want everything else to change as it does but obviously as I am trying to calculate the value of column L I would like the L1 to stay as L1

Perhaps a macro would be easier, what do you think?
but how would I do the macro I tried recording bit didnt get what I expected

 

[Sypher2]

Try this in your first cell:

=IF(I2="total",SUM($L$1:L2),IF(D2=0,"",J2+K2))

Then drag it down. The absolute reference ($L$1) will make it so it doesn't change as you fill down.