Adding OR to Formula with IF AND

MarjorieP

Hello Community,

I am trying to add an OR option to this formula which currently works for provisioning without it. Basically I want to add Customer Country is not blank OR Project Type = Demo / Customer Evaluation.

Formula should allow provisioning if Project Manager at row is not blank, Project Keyword is not blank, (Customer Country Ship to is not blank OR Project Type is Demo / Customer Evaluation), Project State not Rejected

=IF(AND([Ready for Provisioning]@row = 1, NOT(ISBLANK([Project Manager]@row)), NOT(ISBLANK([Project Keyword]@row)), NOT(ISBLANK([Customer Country (Ship to)]@row)), [Project State]@row <> "Rejected"), 1, 0)

Everything I have tried has resulted in errors. :(

jmyzk_cloudsmart_jp

Hi @MarjorieP

If the Project Type is Demo, then Customer Country (Ship to) is not needed. 

Do you mean ''If the Project Type is Demo / Customer Evaluation, then Customer Country (Ship to) is not needed. ?"

If so;

=IF(AND([Ready for Provisioning]@row = 1, NOT(ISBLANK([Project Manager]@row)), NOT(ISBLANK([Project Keyword]@row)), [Project State]@row <> "Rejected", OR(NOT(ISBLANK([Customer Country (Ship to)]@row)), [Project Type]@row = "Demo / Customer Evaluation")), 1, 0)

=IF(
    AND(
        [Ready for Provisioning]@row = 1,
        NOT(ISBLANK([Project Manager]@row)),
        NOT(ISBLANK([Project Keyword]@row)),
        [Project State]@row <> "Rejected",
        OR(
            NOT(ISBLANK([Customer Country (Ship to)]@row)),
            [Project Type]@row = "Demo / Customer Evaluation"
        )
    ),
    1,
    0
)

jmyzk_cloudsmart_jp

Happy to help!😁 @MarjorieP

jmyzk_cloudsmart_jp

Hi @MarjorieP

If "Project Type is Demo / Customer Evaluation), Project State not Rejected" means "Project Type is "Demo" or "Customer Evaluation" or "Demo / Customer Evaluation", and Projet State not Rejected, the formula is as follows;

=IF(

OR(

AND([Ready for Provisioning]@row = 1, NOT(ISBLANK([Project Manager]@row)), NOT(ISBLANK([Project Keyword]@row)), NOT(ISBLANK([Customer Country (Ship to)]@row)), [Project State]@row <> "Rejected"),

AND(OR([Project Type]@row = "Customer Evaluation", [Project Type]@row = "Demo", [Project Type]@row = "Demo / Customer Evaluation"), [Project State]@row <> "Rejected")

),

1, 0)

https://app.smartsheet.com/b/publish?EQBCT=2be84b07916f4ed3b7b2037fb5bfa39f

MarjorieP

Hi @jmyzk_cloudsmart_jp

Thank you for providing a solution. Apologies for the delay in response. I used the formula as presented and it seems to work. The Option "Demo / Customer Evaluation" is one selection. When I removed the other selections "Demo" and "Customer Evaluation" the formula returned "unparseable" . What am I missing?

I want to be sure that:

If the Project Type is Demo, then Customer Country (Ship to) is not needed. All else (Project Manager and Project Keyword are not blank and Project State is not Rejected) are needed.

=IF(OR(AND([Ready for Provisioning]@row = 1, NOT(ISBLANK([Project Manager]@row)), NOT(ISBLANK([Project Keyword]@row)), NOT(ISBLANK([Customer Country (Ship to)]@row)), [Project State]@row <> "Rejected"), AND(OR([Project Type]@row = “Demo / Customer Evaluation”), [Project State]@row <> “Rejected”)), 1, 0)

jmyzk_cloudsmart_jp

Hi @MarjorieP

If the Project Type is Demo, then Customer Country (Ship to) is not needed. 

Do you mean ''If the Project Type is Demo / Customer Evaluation, then Customer Country (Ship to) is not needed. ?"

If so;

=IF(AND([Ready for Provisioning]@row = 1, NOT(ISBLANK([Project Manager]@row)), NOT(ISBLANK([Project Keyword]@row)), [Project State]@row <> "Rejected", OR(NOT(ISBLANK([Customer Country (Ship to)]@row)), [Project Type]@row = "Demo / Customer Evaluation")), 1, 0)

=IF(
    AND(
        [Ready for Provisioning]@row = 1,
        NOT(ISBLANK([Project Manager]@row)),
        NOT(ISBLANK([Project Keyword]@row)),
        [Project State]@row <> "Rejected",
        OR(
            NOT(ISBLANK([Customer Country (Ship to)]@row)),
            [Project Type]@row = "Demo / Customer Evaluation"
        )
    ),
    1,
    0
)

MarjorieP

Hello @jmyzk_cloudsmart_jp

Thank you so very much for your help! Appreciate it immensely.

_MP

jmyzk_cloudsmart_jp

Happy to help!😁 @MarjorieP