Determining Final Approval Status

I have an approval flow that can go to one of three contacts (sometimes two). I want to add a Final Status column to make it easier to count in a summary sheet and eventual put it on a dashboard. I'm trying to create a formula with the following logic:


If any of the three columns contains "Denied", the Final Status is "Red" (no need to continue)

If any of the three columns contains "Approved" but not Denied, then Final Status is "Green"

If any of the three columns contains "Pending" but not Approved or Denied, then Final Status is "Yellow"

Otherwise Final Status is "Grey"


Something simple like this works fine:

=IF(COUNTIF([Leader Approval Status]1:[Sr Mgr Approval Status]1, "Denied"), "Red", "Grey")


But I get UNPARSABLE for something like this:

=IF(COUNTIF([Leader Approval Status]1:[Sr Mgr Approval Status]1, "Denied"), "Red"), IF(COUNTIF([Leader Approval Status]1:[Sr Mgr Approval Status]1, "Approved"), "Green"), IF(COUNTIF([Leader Approval Status]1:[Sr Mgr Approval Status]1, "Pending"), "Yellow", "Grey")))


I've attached an image of what I'd like to see by manually setting the value for Final Status. I've looked at this for hours and I'm not having any luck. Any help would be appreciated.


Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!