Update project status column based on the information in multiple columns, using IF, AND, ISERROR

Alexis Kipping

I have a formula using IF, AND, ISERROR, and COUNT in excel (see below), which works.

=IF(AND(ISERROR(FIND("Cancelled",AE2&AP2&AY2&BG2,1)),ISERROR(FIND("On-Hold",AE2&AP2&AY2&BG2,1)),K2<>"NSG"),"Stage "&COUNTA(AE2,AP2,AY2,BG2)+1,"n/a")

I'm trying to update the formula to work in smartsheet, and have updated to this point, and can't figure out where I am going wrong. I keep on getting #UNPARSEABLE. I feel I'm missing a parenthesis or comma somewhere, but can't figure out where.

This is the formula I have in smartsheet currently

=IF(AND(ISERROR(FIND("Cancelled",[NPR Decision]@row&[Gate 3 Decision]@row&[Gate 4 Decision]@row&[Gate 5 Decision]@row,1))),(ISERROR(FIND("On-Hold",[NPR Decision]@row&[Gate 3 Decision]@row&[Gate 4 Decision]@row&[Gate 5 Decision]@row,1))),Project Type<>"NSG","Stage"&COUNT([NPR Decision]@row,[Gate 3 Decision]@row,[Gate 4 Decision]@row,[Gate 5 Decision]@row)+1,"n/a")

I am also open to any other ideas/suggestions to achieve a similar end. Thank you in advance :)

Paul Newcome

Great! That really simplifies things a lot!

So here is the logic:

If the Project Type = "NSG" then output "NSG".

If the above is not true and if one or more of the NPR, Gate 3, Gate 4, or Gate 5 Decision fields are "Cancelled" or "On-hold" then output "n/a".

If the above is not true and if Gate 5 is "Approved" then output "Stage 5".

If the above is not true and if Gate 4 is "Approved" then output "Stage 4".

If the above is not true and if Gate 3 is "Approved" then output "Stage 3".

If the above is not true and if NPR is "Approved" then output "Stage 2".

And here is the formula:

=IF([Project Type]@row = "NSG", "NSG", IF(OR(CONTAINS("Cancelled", [NPR Decision]@row:[Gate 5 Decision]@row), CONTAINS("On-hold", [NPR Decision]@row:[Gate 5 Decision]@row)), "n/a", "Stage " + (COUNTIFS([NPR Decision]@row:[Gate 5 Decision]@row, "Approved") + 1)))

And to read the formula:

If the Project Type = "NSG" then output "NSG".

If the above is not true and if one or more of the NPR, Gate 3, Gate 4, or Gate 5 Decision fields are "Cancelled" or "On-hold" then output "n/a".

If the above is not true then output "Stage " and the number of times "Approved" is found +1.

How does that look?