IF CONTAINS Issue

I have a formula in my Purchasing Log sheet that will look at the Vendor Name and compare it to Supplier Name in the Approved Supplier List and if the Vendor is in the Approved Supplier List will fill a check box. It works well but I want to add another condition to only check the box if the Supplier Status Column in the Approved Supplier List contains "Approved".

The formula needs updating since I now have vendors on the Approved Supplier List that have been Inactivated. The way the formula currently sits, this check box will be marked even if the vendor has been made inactive. I don't want that to happen.

Here is my current formula

=IF(CONTAINS(Vendor@row, {Approved Supplier List Range 1}), 1)

Thank you in advance for any help.

Best Answer

  • Kelly Moore
    Kelly Moore Community Champion
    Answer ✓

    Hey Chris

    Do both of the 'Approved' conditions contain the word 'Approve'? If yes, you can also write it as a CONTAINS. Recall that the CONTAINS function will pick up any variation of the search term.

    We can use an OR if needed. However, if the CONTAINS solves it, since you're already familiar with that function, that would be the way I would go.

    =IF(COUNTIFS({Approved Supplier List Range 1},CONTAINS(Vendor@row, @cell), {Approved Supplier List Supplier Status}, CONTAINS("Approve", @cell))>0, 1)

Answers

  • Kelly Moore
    Kelly Moore Community Champion

    Hey Chris

    =IF(COUNTIFS({Approved Supplier List Range 1},CONTAINS(Vendor@row, @cell), {Approved Supplier List Supplier Status}, "Approved")>0, 1)

    This says if the two conditions are found (which means the count will be more than zero), then check the box.

    Remember since this is a cross sheet reference, you cannot simply cut and paste - you must build the cross sheet reference manually.

    Kelly

  • Kelly

    Formula works fantastic. How would I account for Conditionally Approved in the {Approved Supplier List Supplier Status} along with Approved? Is there an OR statement in the formula or would it be adding another CONDITION statement in the formula? I've tried some various ideas but to no avail.

  • Kelly Moore
    Kelly Moore Community Champion
    Answer ✓

    Hey Chris

    Do both of the 'Approved' conditions contain the word 'Approve'? If yes, you can also write it as a CONTAINS. Recall that the CONTAINS function will pick up any variation of the search term.

    We can use an OR if needed. However, if the CONTAINS solves it, since you're already familiar with that function, that would be the way I would go.

    =IF(COUNTIFS({Approved Supplier List Range 1},CONTAINS(Vendor@row, @cell), {Approved Supplier List Supplier Status}, CONTAINS("Approve", @cell))>0, 1)

  • Kelly

    Using CONTAINS worked perfect. Thank you.

    During this endeavor I also discovered making formulas into column formulas. This should alleviate many issues experienced by our organization.

  • Kelly Moore
    Kelly Moore Community Champion

    Glad it worked, and yes, try to use column formulas whenever possible.

    cheers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!