Determining Final Approval Status

Robert Francher

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.

Debbie Sawyer

Try this:

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

I reset up your scenario and using this function it worked for me.

Hope this helps

Kind regards

​​Debbie Sawyer Consultant & Training Manager

 

Debbie Sawyer

Try this:

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

I reset up your scenario and using this function it worked for me.

Hope this helps

Kind regards

​​Debbie Sawyer Consultant & Training Manager

 

Paul Newcome

@Debbie Sawyer I have never seen a COUNTIF used as a standalone for the criteria in an IF statement.

I have always specified

=IF(COUNTIFS(............) > 0, "output")

It's interesting that it works that way.

Debbie Sawyer

to be honest - i havn't either! But I just took the formula that the client posted and worked on it :)

It does work! just a different method - maybe easier for the client to understand as they came up with it to begin with.

Every day is a learning day! ha ha

Robert Francher

It works! You guys are the best!!! Very much appreciated.