Google-sheets – Conditional format custom formula in Google Sheets

conditional formattingformulasgoogle 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.

Best Answer

  • 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")))))))))
Related Topic