So I'm trying to build out a simple campaign tracker and I've been running into a few issues with the conditional formatting on google sheets.

I need the cells on column C to change color and format based on the values in columns A & B. For example. columns A & B are labelled "KPI" & "Target KPI". Suppose the KPI is Click Through Rate, and the Target is .10%, the value in C1 needs to be a % and turn red when LOWER than the Target and Green if higher. However, if the KPI is "Cost Per Conversion" the value in C1 needs to be $ and turn red if it is HIGHER than the Target, and Green if lower.

NOTE: The red and green are performance indicators. Which need to change dynamically if the actual is >/< than the KPI

Hope someone's had this problem and figured out a solution for it! Thanks your help!

Link to a sample (Note the columns I spoke about are H - J on this sheet - https://docs.google.com/spreadsheets/d/1D4T7uKql55nc8eb9cHPUbPbQMnvwZ3FPJLB9LI0uA0s/edit?usp=sharing


1 Answer 1


=IF(IFERROR(FILTER(I2, SEARCH("*%*", I2)))*(J2 < I2), 1)
=IF(IFERROR(FILTER(I2, SEARCH("*%*", I2)))*(J2 > I2), 1)
=COUNTIF(J2, "<"&I2)
=COUNTIF(J2, ">"&I2)

  • 1
    Awesome! This works well! Thanks man! Apr 3, 2019 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.