MrExcel Publishing
Your One Stop for Excel Tips & Solutions

sum above

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


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


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


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


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


Try using the absolute form of the cell address ie


Any help?