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

Alexis Kipping
Alexis Kipping โœญโœญโœญโœญ
edited 01/04/21 in Formulas and Functions

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 :)

Best Answer

  • Paul Newcome
    Paul Newcome Community Champion
    Answer โœ“

    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?

ยซ1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!