Expert Needed: Conditional Formatting and SumIF/VlookUp issue (Last row)

Hey All,

I am trying to conditional format the top right chart "Asset Class Exposure" so that each asset class will turn red when the sum of all the respective New Target Change From Current (%) is <0, and green when it is >0.

Here is the issue I am having since the column is in the furthest right row (And it must remain there) I am unable to use a VLOOKUP.

I tried using the following formula

=IF(SUMIF($L$14:$L$25,K9,$J$14:$J$25)<0,"Sell",IF(SUMIF($L$14:$L$25,K9,$J$14:$J$25)>0,"Buy","")) and then using a conditional format ="Buy" format green (vice versa for red)

However for Asset classes with multiple when summing it sometimes doesnt sum correctly. Please see the second screen capture. (1+1-2=0) Therefore the Equity box should remain white.

What am i doing wrong and how can I fix this.

The number on top of the "New Target Weight" box is the value of the forumla seen in the formula box (i.e the same formula used in the conditional format)

I really appreciate any solution you can offer me.

*Note the New Target Change From Current calculates itself based on a new value typed in New Target Weight.Capture.PNGCapture1.PNG

Thank you

Sorry about that. Find the excel sheet attached

Does anyone else have any idea on how i can work around that