Formula for RYGG symbol based on RYGG symbols in 6 other columns

Lori Flanigan

balls. The 6 other subcategories/column names are:

1. Financial Status
2. Quality Status
3. Staffing Status
4. Delivery/Operations Status
5. Customer Satisfaction Status
6. Subcontractor Management Status

The Overall Status is green if all subcategories are green.

The Overall Status is yellow if 1-3 subcategories are yellow.

The Overall Status is red if 4+ subcategories are yellow or 1+ subcategories are red..

Here is the formula that I’ve completed that works correctly; however, when I try to add the next (IF(AND)) part, it stops as if I’ve hit a maximum number of characters. Is that the case? If so, is there a shortcut that I’m not finding to make this more manageable?

=IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Green", IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Yellow", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Green", [Staffing Status]@row = "Yellow", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Yellow", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Yellow", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Yellow"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Yellow", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Green", [Staffing Status]@row = "Yellow", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Yellow", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Yellow", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Yellow"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Yellow", [Staffing Status]@row = "Yellow", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Yellow", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Yellow", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Yellow", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Yellow", [Subcontractor Management Status]@row = "Green"), "Yellow")))))))))))))))))))))))))))

 

Thanks,

Lori

Andrée Starå

Hi Lori,

Yes, it seems like your hitting the 4000 character limit.

I can see that your formula could be simplified.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

 

Lori Flanigan

Thank you for your reply! I'm happy to share a screenshot of the sheet (see attached). I appreciate your help with this!

Lori