up vote 1 down vote favorite

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.

share|improve this question
up vote 0 down vote
  • 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")))))))))
share|improve this answer
  • Thank you very much. I added it to the Conditional Format Rules but it did not turn green..any thoughts – user190579 Jul 12 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 – user190579 Jul 12 at 20:55
  • It did not like that either. I've not seen the " around the >= here either..will try to remove – user190579 Jul 12 at 20:57
  • did not like that either – user190579 Jul 12 at 20:59
  • 1
    did work..thanks everyone! – user190579 Jul 12 at 21:24

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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