Formula for Status signs based on multiple columns

Adrian Garcia

@Paul Newcome I have this formula to change the Status column depending on three approval columns. It works but I want it to do the same thing for denied.

Status formula:

=IF(AND([TUSC Operations Approval]@row = "Approved", [Customer Experience Approval]@row = "Approved", [Demo Host Approval]@row = "Approved"), "Confirmed", "Pending")

How can I have both in the same formula?

So it should be if all three have "Denied", then show "Denied, if not all three then show "Pending"

Paul Newcome

@Adrian Garcia Try this...

=IF(AND([TUSC Operations Approval]@row = "Denied", [Customer Experience Approval]@row = "Denied", [Demo Host Approval]@row = "Denied"), "Denied", IF(AND([TUSC Operations Approval]@row = "Approved", [Customer Experience Approval]@row = "Approved", [Demo Host Approval]@row = "Approved"), "Confirmed", "Pending"))

Ayperi Uzun

@Paul Newcome Hi Paul,

I have 6 approval columns next to each other and not all are in the approval process for every event. So, in some cases 3 out of 6 approval cells will be blank, in some cases all will be involved. I want my formula to ignore blank cells and decide if the event is approved or not.

If we use this one as a base for example: Sometimes operations is not part of approval but other 2 are. =IF(AND([TUSC Operations Approval]@row = "Denied", [Customer Experience Approval]@row = "Denied", [Demo Host Approval]@row = "Denied"), "Denied", IF(AND([TUSC Operations Approval]@row = "Approved", [Customer Experience Approval]@row = "Approved", [Demo Host Approval]@row = "Approved"), "Confirmed", "Pending"))

How can I modify my formula?

Thanks for your answer :)

Paul Newcome

@Ayperi Uzun If they are all right next to each other and we are ignoring blanks, you can use something along these lines...

=IF(COUNTIFS([1st Approval Column]@row:[Last Approval Column]@row, @cell = "Denied") + COUNTIFS([1st Approval Column]@row:[Last Approval Column]@row, @cell <> ""), "Denied", IF(COUNTIFS([1st Approval Column]@row:[Last Approval Column]@row, @cell = "Approved") + COUNTIFS([1st Approval Column]@row:[Last Approval Column]@row, @cell <> ""), "Approved", "Pending"))

Basically we count how many are "Denied" and compare that to how many are not blank. If those numbers match then all non-blank cells must be "Denied". Then we use that same logic for "Approved" and finally everything else is "Pending".