Issue with IF AND formula

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

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

Engagement Phase dropdown column with "In progress", "On-hold", "completed" etc statuses.

Production status column with values "In Prod", "Prod Ineligible" etc

Conversion column - which needs to set to 0 for Prod Ineligible - in order to not be counted in Total SUM.

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.


  • Sameer K
    Sameer K ✭✭✭✭

    Are you sure the values you are comparing in your AND function are exactly the same (case too)? For example, your drop down values are "COMPLETED" and "Completed" or "Production Ineligible" and not "Prod Ineligible'?

  • No the column names are as in the formula. I was short cutting in the message above.

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

    Now if the Engagement Phase is "In Progress" and Production Status is in Production - It sets 1.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!