Nested IF / AND / OR formula help needed
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
Best Answers

Hi Mary
Try this
=IF(OR([Electronic Claim Submission]@row = "", [Electronic Payment Posting]@row = "", [Paper Payment Posting]@row = "", [Accounts Receivable Follow Up]@row = "", [Denial Management Resolution]@row="", [Other SLA]@row=""), "", IF(OR([Electronic Claim Submission]@row = "Red", [Electronic Payment Posting]@row = "Red", [Paper Payment Posting]@row = "Red", [Accounts Receivable Follow Up]@row = "Red", [Denial Management Resolution]@row="Red", [Other SLA]@row="Red"), "Red", IF(OR([Electronic Claim Submission]@row = "Green", [Electronic Payment Posting]@row = "Green", [Paper Payment Posting]@row = "Green", [Accounts Receivable Follow Up]@row = "Green", [Denial Management Resolution]@row="Green", [Other SLA]@row="Green"), "Green", IF(OR([Electronic Claim Submission]@row = "Gray", [Electronic Payment Posting]@row = "Gray", [Paper Payment Posting]@row = "Gray", [Accounts Receivable Follow Up]@row = "Gray", [Denial Management Resolution]@row="Gray", [Other SLA]@row="Gray"), "Green"))))
We probably could have left one of the series off and defaulted the last False value to that, however I noticed Yellow wasn't an option and I wasn't sure if it was a possibility.
Kelly

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!

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 My mistake, when doing COUNTIFS I often put 1 when I mean 0...
So here is the right one, there's the same problem with Blank cells:
=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")))
Answers

Hi Mary
Try this
=IF(OR([Electronic Claim Submission]@row = "", [Electronic Payment Posting]@row = "", [Paper Payment Posting]@row = "", [Accounts Receivable Follow Up]@row = "", [Denial Management Resolution]@row="", [Other SLA]@row=""), "", IF(OR([Electronic Claim Submission]@row = "Red", [Electronic Payment Posting]@row = "Red", [Paper Payment Posting]@row = "Red", [Accounts Receivable Follow Up]@row = "Red", [Denial Management Resolution]@row="Red", [Other SLA]@row="Red"), "Red", IF(OR([Electronic Claim Submission]@row = "Green", [Electronic Payment Posting]@row = "Green", [Paper Payment Posting]@row = "Green", [Accounts Receivable Follow Up]@row = "Green", [Denial Management Resolution]@row="Green", [Other SLA]@row="Green"), "Green", IF(OR([Electronic Claim Submission]@row = "Gray", [Electronic Payment Posting]@row = "Gray", [Paper Payment Posting]@row = "Gray", [Accounts Receivable Follow Up]@row = "Gray", [Denial Management Resolution]@row="Gray", [Other SLA]@row="Gray"), "Green"))))
We probably could have left one of the series off and defaulted the last False value to that, however I noticed Yellow wasn't an option and I wasn't sure if it was a possibility.
Kelly

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!

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")))

Kelly,
Thank you! This formula worked as well!
Mary

@RCM Admin My mistake, when doing COUNTIFS I often put 1 when I mean 0...
So here is the right one, there's the same problem with Blank cells:
=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")))

David  Perfect! Thank you so much.
Mary

@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
Categories
Check out the Formula Handbook template!