Using IF and OR together

I am very new to smartsheet and I am trying to condition a cell to be "Yes" or "No" based on items noted in a specific column


=IF(CONTAINS("ItemA", Order:Order), "Yes", "No")


...but I also want to include OR IF "Item B" OR IF "item C" is there


The don't all have to be there to be "Yes" just at least one of them


Thank you in advance!

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/18/22

    @Sam Walsh

    Question 1: Is the string you're looking for just part of the possible value in a cell in the Order column, or is the whole cell equal to ItemA, or ItemB, or ItemC?

    Question 2: Are you checking to see if ANY rows in the Order column contain those values, or if just the particular row you're on does?

    If you want to check the whole column for the presence of one or more cells that equal any of your three values, you can do this without CONTAINS, but you'll need a COUNTIF in there:

    =IF(COUNTIF(Order:Order, OR(@cell = "ItemA", @cell = "ItemB", @cell = "ItemC")) > 1, "Yes", "No")

    If you want to check the whole column for the presence of one or more cells that contain any of your three values, you can use CONTAINS:

    =IF(OR(CONTAINS("ItemA", Order:Order), CONTAINS("ItemB", Order:Order), CONTAINS("ItemC", Order:Order)), "Yes", "No")

    If you just want to check the Order cell on the particular row for your values:

    =IF(OR(Order@row = "ItemA", Order@row = "ItemB", Order@row = "ItemC"), "Yes", "No")

    If you just want to check if the Order cell on the particular row contains one or more of your values:

    =IF(CONTAINS(Order@row = "ItemA", Order@row = "ItemB", Order@row = "ItemC"), "Yes", "No")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Sam Walsh
    Sam Walsh ✭✭✭

    Question 1: Is the string you're looking for just part of the possible value in a cell in the Order column, or is the whole cell equal to ItemA, or ItemB, or ItemC?

    A, B or C would be individual cells in the Order column by themselves

    Question 2: Are you checking to see if ANY rows in the Order column contain those values, or if just the particular row you're on does?

    Yes, any of the rows (individual cells) in the order column contain A, B or C would be "Yes"-- if none are contained in the order column then "No"

  • Sam Walsh
    Sam Walsh ✭✭✭

    If you want to check the whole column for the presence of one or more cells that contain any of your three values, you can use CONTAINS:

    =IF(OR(CONTAINS("ItemA", Order:Order, CONTAINS("ItemB", Order:Order), CONTAINS("ItemC", Order:Order)), "Yes", "No")


    I believe this is what I need or close to

  • Sam Walsh
    Sam Walsh ✭✭✭

    From the "Yes" results in my sheet-- I want to generate a report that pulls PDFs tied to the "Yes" results

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/18/22

    Since "A, B or C would be individual cells in the Order column by themselves," either of the first two formulas would work. The first one does it faster with less system overhead.

    =IF(COUNTIF(Order:Order, OR(@cell = "ItemA", @cell = "ItemB", @cell = "ItemC")) > 1, "Yes", "No")

    The formula with CONTAINS in there three times with have to evaluate the entire column three times, vs once for the first formula. In a big sheet that can cause slowness/reduced response time.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!