# Conditional format custom formula in Google Sheets

Have a question on how to do a custom formula in conditional format for the following:

``````A1 = DCC220
B1 = 7.50
C1 = 7.00
D1 = 7.60
``````

I want to compare the result in B1 (7.50) to a range, C1 lower, D1 upper, but only if the value in A1 is DCC222. Color green if B1 is in the range of C1 and D1 and red if it is not.

• custom formula for green: `=COUNTIFS(A1; "DCC220"; B1; ">"&C1 ; B1; "<"&D1)`
• and for red: `=OR(COUNTIFS(A1; "DCC220"; B1; "<"&C1); COUNTIFS(A1; "DCC220"; B1; ">"&D1))`

bonus knowledge: to include C1 and D1 in the range as well, you can add equal sign like this:

• custom formula for green: `=COUNTIFS(A1; "DCC220"; B1; ">="&C1 ; B1; "<="&D1)`

# FINAL PRODUCT OF OP'S QUESTION:

CUSTOM FORMULA FOR GREEN APPLIED IN E1 FOR RANGE E:E

``````=ArrayFormula(OR(COUNTIFS(B1, INDIRECT("Index!\$A\$2"), E1, ">="&INDIRECT("Index!\$B\$2"), E1, "<="&INDIRECT("Index!\$C\$2")),
COUNTIFS(B1, INDIRECT("Index!\$A\$3"), E1, ">="&INDIRECT("Index!\$B\$3"), E1, "<="&INDIRECT("Index!\$C\$3")),
COUNTIFS(B1, INDIRECT("Index!\$A\$4"), E1, ">="&INDIRECT("Index!\$B\$4"), E1, "<="&INDIRECT("Index!\$C\$4")),
COUNTIFS(B1, INDIRECT("Index!\$A\$5"), E1, ">="&INDIRECT("Index!\$B\$5"), E1, "<="&INDIRECT("Index!\$C\$5")),
COUNTIFS(B1, INDIRECT("Index!\$A\$6"), E1, ">="&INDIRECT("Index!\$B\$6"), E1, "<="&INDIRECT("Index!\$C\$6"))))
``````

CUSTOM FORMULA FOR RED APPLIED IN E1 FOR RANGE E:E

``````=ArrayFormula(OR(OR(COUNTIFS(B1, INDIRECT("Index!\$A\$2"), E1, "<"&INDIRECT("Index!\$B\$2")), COUNTIFS(B1, INDIRECT("Index!\$A\$2"), E1, ">"&INDIRECT("Index!\$C\$2")),
OR(COUNTIFS(B1, INDIRECT("Index!\$A\$3"), E1, "<"&INDIRECT("Index!\$B\$3")), COUNTIFS(B1, INDIRECT("Index!\$A\$3"), E1, ">"&INDIRECT("Index!\$C\$3")),
OR(COUNTIFS(B1, INDIRECT("Index!\$A\$4"), E1, "<"&INDIRECT("Index!\$B\$4")), COUNTIFS(B1, INDIRECT("Index!\$A\$4"), E1, ">"&INDIRECT("Index!\$C\$4")),
OR(COUNTIFS(B1, INDIRECT("Index!\$A\$5"), E1, "<"&INDIRECT("Index!\$B\$5")), COUNTIFS(B1, INDIRECT("Index!\$A\$5"), E1, ">"&INDIRECT("Index!\$C\$5")),
OR(COUNTIFS(B1, INDIRECT("Index!\$A\$6"), E1, "<"&INDIRECT("Index!\$B\$6")), COUNTIFS(B1, INDIRECT("Index!\$A\$6"), E1, ">"&INDIRECT("Index!\$C\$6")))))))))
``````
• Thank you very much. I added it to the Conditional Format Rules but it did not turn green..any thoughts Jul 12, 2018 at 20:37
• How would we do it if we just put the values in ex >=7.00 and >=7.60. I have conditional statements in my actual document for c1 and d1 that the conditional format rules function does not recognize Jul 12, 2018 at 20:55
• It did not like that either. I've not seen the " around the >= here either..will try to remove Jul 12, 2018 at 20:57
• did not like that either Jul 12, 2018 at 20:59
• did work..thanks everyone! Jul 12, 2018 at 21:24