Formulas to highlight cells

New Contributor

Hello! Thank you so much in advance for your help!


I have a cell in A1 that contains:


Composer A (BMI) 50% [292650159], Composer B (ASCAP) 30% [154541151], Composer C (SESAC) 10% [55547898], Composer D (SESAC) 5% [4125475], Composer E (SOCAN) 5% [02124054]


1) I'd need a formula to use in Conditional Formatting that will turn this cell green if the sum of the % equals 100%.

2) I'd need another formula to turn the cell red if the sum doesn't equal 100%, and no highlighting if the cell is empty.


*** I have another cell in B1 that contains:


Publisher A (BMI) 50% [612559885], Publisher B (ASCAP) 35% [02135474], Publisher C (SESAC) 15% [01023545]


I'd need a formula to highlight the cell in green if the percentages for BMI and SESAC match the ones in the previous cell (A1) for composers and if the sum of all the % in this publisher cell equals 100% (SOCAN composers go under the ASCAP publisher in this example that's why there is no Publisher for SOCAN). 


The cell should be red if there is an issue with the % splits.


I really appreciate your help! Thanks again!


0 Replies