How to use IF function with AND OR

Options

I am trying to establish a workflow based on the approval of several stakeholders. There are 8 stakeholders (contact column type) and not all will be required to review/approve. If the stakeholders approve the change the project sponsor will be required to approve. I intend to have a workflow for each stakeholder to "approve" or "decline". If no stakeholder is identified the row is to be filled "not applicable". How do I write the if statement to include a summarizing column of stakeholders approval based on each stakeholders assessment.

I have tried starting with the logic for 1 stakeholder and then expanding for all but I am not having any success at step 1. Here is my initial calc:

=IF(OR([Stakeholder 1 approval]7 = "Approved", [Stakeholder 1 approval]7 = "Not Applicable", "Approved", "Declined"))

any tips for someone new to Smartsheet?

Best Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @deej

    Looking only at the initial calc, I suspect the closing bracket for the OR function should be between Not Applicable and Approved - like this:

    =IF(OR([Stakeholder 1 approval]7 = "Approved", [Stakeholder 1 approval]7 = "Not Applicable"), "Approved", "Declined")

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • deej
    deej ✭✭
    Answer ✓
    Options

    I forgot the comma between )) and "Approved" towards the end.

    =IF(AND(OR([Stakeholder 1 approval]8 = "Approved", [Stakeholder 1 approval]8 = "Not Applicable"),OR([Stakeholder 2 approval]8 = "Approved", [Stakeholder 2 approval]8 = "Not Applicable")), "Approved", "Declined")

    this seems to work. thanks for your guidance!

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @deej

    Looking only at the initial calc, I suspect the closing bracket for the OR function should be between Not Applicable and Approved - like this:

    =IF(OR([Stakeholder 1 approval]7 = "Approved", [Stakeholder 1 approval]7 = "Not Applicable"), "Approved", "Declined")

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • deej
    deej ✭✭
    Options

    Thank you, works perfectly. How would you suggest stringing together multiple stakeholders? I am trying using the AND statement but again not having much luck.

    =IF(AND(OR([Stakeholder 1 approval]8 = "Approved", [Stakeholder 1 approval]8 = "Not Applicable"),OR([Stakeholder 2 approval]8 = "Approved", [Stakeholder 2 approval]8 = "Not Applicable")) "Approved", "Declined")

  • deej
    deej ✭✭
    Answer ✓
    Options

    I forgot the comma between )) and "Approved" towards the end.

    =IF(AND(OR([Stakeholder 1 approval]8 = "Approved", [Stakeholder 1 approval]8 = "Not Applicable"),OR([Stakeholder 2 approval]8 = "Approved", [Stakeholder 2 approval]8 = "Not Applicable")), "Approved", "Declined")

    this seems to work. thanks for your guidance!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!