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

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

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

David Joyeuse

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

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

David Joyeuse

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

All comments

Kelly Moore

Hi Mary

Try this

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

David Joyeuse

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

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:

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

Kelly,

Thank you! This formula worked as well!

Mary

David Joyeuse

@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:

RCM Admin

David - Perfect! Thank you so much.

Mary

RCM Admin

@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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.