Nested IF / AND / OR formula help needed

Options

I need to create a formula which drives the RGG ball in the Contact Compliance Status" field based upon RGG values in the previous 6 columns, based upon the following.

Possible Values in the 6 previous columns will be Green, Red, Gray or blank:


I've tried several versions of =IF(AND(OR ... but haven't been successful. Any assistance would be greatly appreciated!

Thanks!

Mary F

Tags:

Best Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Answer ✓
    Options

    Hi @RCM Admin

    That's one hell of a formula from @KDM.

    Let's sum this up a little bit for some clarity.

    Criteria 1 is that you want your cell to be red if there's at least one red in your range. Instead of going with OR, let's go with COUNTIFS here.

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, "Red")>1, "Red")

    Criteria 2: If all values are "Green" or "Gray", then "Green"

    Same, let's go with a COUNTIFS

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, OR(@cell="Green", @cell="Gray"))=6, "Green")

    Criteria 3: If at least one value is blank, return blank.

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, ISBLANK(@cell))>1, "")

    OK, now I want you to notice, that there's no options in Smartsheet with RGG, it's either RYG ("Red", "Yellow", "Green") or RYGG ("Red", "Yellow", "Green", "Gray").

    Which one are you using? Former and you mistaken Yellow with Gray? or the latter one but you're not using "Yellow"?

    Depending, it may change the formula a small bit. If you're going with 3 values, instead of 4, then we can get rid of criteria 2, because if all cells have a value (i.e. aren't blank) and none is red, it means they are all either yellow or green.

    But if you go with RYGG, then your criteria are completly putting the yellow aside and the formula won't "work" if there's some yellow within any cell of your range.

    To sum it up for the RYGG stuff:

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, ISBLANK(@cell))>1, "", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, "Red")>1, "Red", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, OR(@cell="Green", @cell="Gray"))=6, "Green")))

    And if you're really RYG:

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, ISBLANK(@cell))>1, "", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, "Red")>1, "Red", "Green"))

    Hope it helped!

  • RCM Admin
    RCM Admin ✭✭
    Answer ✓
    Options

    David - This was very helpful - I appreciate the explanation. I did make one edit however, because the ball would only turn red if I had more than one preceding red ball, so instead of =1, I inserted symbols for greater or equal to 1, and it worked well.


    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, ISBLANK(@cell)) > 1, "", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, "Red") >= 1, "Red", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, OR(@cell = "Green", @cell = "Gray")) = 6, "Green")))


Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Answer ✓
    Options

    Hi @RCM Admin

    That's one hell of a formula from @KDM.

    Let's sum this up a little bit for some clarity.

    Criteria 1 is that you want your cell to be red if there's at least one red in your range. Instead of going with OR, let's go with COUNTIFS here.

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, "Red")>1, "Red")

    Criteria 2: If all values are "Green" or "Gray", then "Green"

    Same, let's go with a COUNTIFS

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, OR(@cell="Green", @cell="Gray"))=6, "Green")

    Criteria 3: If at least one value is blank, return blank.

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, ISBLANK(@cell))>1, "")

    OK, now I want you to notice, that there's no options in Smartsheet with RGG, it's either RYG ("Red", "Yellow", "Green") or RYGG ("Red", "Yellow", "Green", "Gray").

    Which one are you using? Former and you mistaken Yellow with Gray? or the latter one but you're not using "Yellow"?

    Depending, it may change the formula a small bit. If you're going with 3 values, instead of 4, then we can get rid of criteria 2, because if all cells have a value (i.e. aren't blank) and none is red, it means they are all either yellow or green.

    But if you go with RYGG, then your criteria are completly putting the yellow aside and the formula won't "work" if there's some yellow within any cell of your range.

    To sum it up for the RYGG stuff:

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, ISBLANK(@cell))>1, "", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, "Red")>1, "Red", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, OR(@cell="Green", @cell="Gray"))=6, "Green")))

    And if you're really RYG:

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, ISBLANK(@cell))>1, "", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, "Red")>1, "Red", "Green"))

    Hope it helped!

  • RCM Admin
    RCM Admin ✭✭
    Answer ✓
    Options

    David - This was very helpful - I appreciate the explanation. I did make one edit however, because the ball would only turn red if I had more than one preceding red ball, so instead of =1, I inserted symbols for greater or equal to 1, and it worked well.


    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, ISBLANK(@cell)) > 1, "", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, "Red") >= 1, "Red", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, OR(@cell = "Green", @cell = "Gray")) = 6, "Green")))


  • RCM Admin
    Options

    Kelly,

    Thank you! This formula worked as well!

    Mary

  • RCM Admin
    Options

    David - Perfect! Thank you so much.

    Mary

  • RCM Admin
    Options

    @David Joyeuse Thanks so very much for your previous help! It works great - however - they want to make a change. If all fields in the range are "Gray", then they want the RYGG ball to be Gray.

    Original formula which you helped with:

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, ISBLANK(@cell)) > 0, "", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, "Red") > 0, "Red", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, OR(@cell = "Green", @cell = "Gray")) = 6, "Green")))

    I tried editing the formula with Gray info per below, but am receiving a #UNPARSEABLE error. Can you help?

    =IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, ISBLANK(@cell)) > 0, "", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, "Red") > 0, "Red", IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, “Gray”) =6, “Gray”, IF(COUNTIFS([Electronic Claim Submission]@row:[Other SLA]@row, OR(@cell = "Green", @cell = "Gray")) = 6, "Green")))

    Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!