IF/ AND yes or no

Hello Friends,

I am looking to program a cell with a formula to indicate "Yes" or "No" for items noted in a certain column containing all Parts in an Order

This will be used to pull report attached to the line item when triggered "yes"


IF Column2 Contains PartA AND PartB "Yes"


Also I might want to program it the opposite way for a "primary part" cell--


IF Cell B2(Primary Part Number) CONTAIN Part A and Column2 (the rest of the order) DOES NOT CONTAIN Part B then "Yes"

For some reason example formula aren't working for me :( I am pretty new to Smartsheet


Thanks in advance!

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Sam Walsh

    Try this:

    =IF(AND(COUNTIF([Column2]:[Column2], "Part A") > 0, COUNTIF([Column2]:[Column2], "Part B") > 0), "Yes", "No")

    In English: IF the count of cells in Column2 equal to "Part A" is greater than 0, AND the count of cells in Column2 equal to "Part B" is greater than 0, then set this cell value to "Yes"; otherwise, set the cell value to "No".

    The AND portion is where you feed the IF multiple logical statements separated by commas, and if they all evaluate to true, the IF executes the positive condition. Alternatively, using OR in place of the AND tells the IF to evaluate the multiple logical statements and if at least one of them evaluates to true, then execute the positive condition.

    For your second request:

    =IF(AND([Column2]2 = "Part A", COUNTIF([Column2]:[Column2], "Part B") = 0), "Yes", "No")

    English: IF Column2 row 2 equals "Part A" AND the count of cells in Column2 equal to "Part B" is zero, set the cell value to "Yes"; otherwise, set the cell value to "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!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Sam Walsh

    Try this:

    =IF(AND(COUNTIF([Column2]:[Column2], "Part A") > 0, COUNTIF([Column2]:[Column2], "Part B") > 0), "Yes", "No")

    In English: IF the count of cells in Column2 equal to "Part A" is greater than 0, AND the count of cells in Column2 equal to "Part B" is greater than 0, then set this cell value to "Yes"; otherwise, set the cell value to "No".

    The AND portion is where you feed the IF multiple logical statements separated by commas, and if they all evaluate to true, the IF executes the positive condition. Alternatively, using OR in place of the AND tells the IF to evaluate the multiple logical statements and if at least one of them evaluates to true, then execute the positive condition.

    For your second request:

    =IF(AND([Column2]2 = "Part A", COUNTIF([Column2]:[Column2], "Part B") = 0), "Yes", "No")

    English: IF Column2 row 2 equals "Part A" AND the count of cells in Column2 equal to "Part B" is zero, set the cell value to "Yes"; otherwise, set the cell value to "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 ✭✭✭

    Thank you! This Helps a lot!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!