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

Options

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

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!