Formula for a PASS or FAIL


I would like to add a formula to the sheet that shows a Pass or a Fail depending on three other columns.

If Mold Present column say "No" & the Seeds per 100g column says "None" or & the Foreign Matter column says "No" mark the cell as a PASS. If Mold Present column says "Yes - Beige" or "Yes-Grey" or "Yes-Black" & the Seeds per 100g column says "<5" & the Foreign Matter column says "Yes" mark the cell as FAIL.

Is this possible?



  • MCorbin
    MCorbin Overachievers Alumni

    Possible, yes... BUT... based on the data screenshot you showed, I'm not sure it's going to work because your screenshot doesn't match your criteria above....

    Here is the formula, based on the data set below (which is copied from your screenshot, but added rows to match your criteria):

    =IF(AND([Mold Present]@row = "No", [Seeds per 100g]@row = "None", [Foreign Matter]@row = "No"), "Pass", IF(AND(OR([Mold Present]@row = "Yes-Beige", [Mold Present]@row = "Yes-Grey", [Mold Present]@row = "Yes-Black"), [Seeds per 100g]@row = "<5", [Foreign Matter]@row = "Yes"), "FAIL", ""))

    Your request above mentions Seeds per 100g "<5", but that's nowhere in your screenshot - you do have 5+. So you may need to tweak that part of the formula, as well as the "Mold Present" descriptions to match your actual data.

    You also don't say what you want to do with the rows that don't fit your criteria. In my formula, they're sent to just be blank.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!