MrExcel Publishing
Your One Stop for Excel Tips & Solutions

sum above


Posted by Loon on January 27, 2002 2:08 PM

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, I tried recording macro but didnt get what I want

thanks


Posted by Aladin Akyurek on January 27, 2002 2:27 PM

It seems you're looking for:

In L2 enter: =IF(I2="Total",SUM($L$1:L2),IF(D2,SUM(J2:K2),""))

Copy down as far as needed.

==================

Posted by Robb on January 27, 2002 2:29 PM

Loon

Try using the absolute form of the cell address ie

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

Any help?

Regards

Robb