Sum of Green & Red Highlighted Cells

Occasional Visitor

I want to calculate the total sum of green highlighted cells and red highlighted cells. if it will change cell color than it should be automatically calculated as color shed. 


Find attached file. (Find Current Profit/Loss Cell in the file)

1 Reply

@dhruvesh18 Couldn't resist changing some of the formulae and deleted many of the conditional formats (see attached).

I assumed you wanted to show the profit/loss in column I as a positive number, but color coded (conditional formatting) to indicate Profit or Loss. That's fine, but then it's better to calculate the underlying number as Qty x (CurrentPrice - Buying Price) and format negative amounts to be displayed without the minus sign (custom format " 0;0; "). The conditional formatting rules will still use the underlying "real" numbers (positive or negative) and the amount in R6 can be a simple sum of the amounts in column I, formatted in the same way (i.e. red or green and no minus sign for negatives).