Syntax Error

=IF(AND([Engagement Phase]:[Engagement Phase]="COMPLETED", [Production Status]:[Production Status]="Production Ineligible") 0,1)


I am sure its something minor.. But just not able to fix this.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 10/19/22 Answer ✓

    Hi @keesuri25

    Can I clarify exactly when you want to see 0 and when you want 1?

    If I'm understanding you correctly, as soon as the Engagement Phase is "Completed" you want it to show 1, EXCEPT if the Production Status is "ineligible".

    If that's correct, we can swap around the 1 and 0 at the end of the formula and adjust it to say <> or NOT equal to "ineligible".


    Try this:

    =IF(AND([Engagement Phase]@row = "COMPLETED", [Production Status]@row <> "Production Ineligible"), 1, 0)


    Should this still not work for you, it would be helpful to know every possible selection in each column and all of the combinations that end up in either 1 or 0.

    Cheers!

    Genevieve

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @keesuri25

    I hope you're well and safe!

    What do you want the formula to do?

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Engagement status - column

    Production status - column

    conversion eligible- column

    So if Engagement status is completed and then for that row Production status is set to Production Ineligible then put 0 in conversion eligible column, if not 1.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @keesuri25

    Do you want to change the value on each row in the conversion eligible column?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • yes. each row on conversion eligible column should change to 1 or 0 depending on the statuses set.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 10/14/22

    @keesuri25

    Try something like this and convert it to a column formula if it works as expected.

    =IF(AND([Engagement Phase]@row = "COMPLETED", [Production Status]@row = "Production Ineligible"), 0, 1)

    Did it work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thank you so much. That worked. Have a great weekend :D

  • The formula is not working completely. I think i need to add an additional clause/check.

    The problem is that if the Engagement Phase is any other status meaning IN Progress or on hold, then the above formula is counting it as Conversion Eligible. As it puts a 1 in conversion eligible column.

    I need to be like only for completed engagements that have a product in eligible then conversion eligible is 0. So any other engagement phase other than completed - conversion eligible is still 0.


    Any thoughts ?

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 10/19/22 Answer ✓

    Hi @keesuri25

    Can I clarify exactly when you want to see 0 and when you want 1?

    If I'm understanding you correctly, as soon as the Engagement Phase is "Completed" you want it to show 1, EXCEPT if the Production Status is "ineligible".

    If that's correct, we can swap around the 1 and 0 at the end of the formula and adjust it to say <> or NOT equal to "ineligible".


    Try this:

    =IF(AND([Engagement Phase]@row = "COMPLETED", [Production Status]@row <> "Production Ineligible"), 1, 0)


    Should this still not work for you, it would be helpful to know every possible selection in each column and all of the combinations that end up in either 1 or 0.

    Cheers!

    Genevieve

  • This worked perfectly. Thank you so much. And sorry about not explaining the context correctly.

    Really appreciate your prompt responses and guidance.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!