COUNTIFS Formula to display a RYGG ball, based upon RYGG colors in range

✭✭✭✭✭

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

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!

• ✭✭✭✭✭✭

It looks like you have "smart quotes" there in the section you added. Notice how they are angled to show open vs closed quotes whereas the other quotes in the formula as well as the quotes in this comment are straight up and down? These are the type of quotes you will need for smartsheet.

You can either retype directly in the sheet or use a text editor such as notepad (instead of word) to replace them with the proper types of quotes.

• ✭✭✭✭✭✭

It looks like you have "smart quotes" there in the section you added. Notice how they are angled to show open vs closed quotes whereas the other quotes in the formula as well as the quotes in this comment are straight up and down? These are the type of quotes you will need for smartsheet.

You can either retype directly in the sheet or use a text editor such as notepad (instead of word) to replace them with the proper types of quotes.

• ✭✭✭✭✭

@Paul Newcome Amazing! Works great. Thank you very much!

• ✭✭✭✭✭✭

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!