Contains or IF formula

I have two columns, one called EP and the other BP. I would like to have the row appear on a report if either column does not have "X", "TBD" or "N/A".

For example, If one column has an "X" and the other has something other than "X", "TBD", or "N/A", I would like the row to appear on the report.

I know I need a helper column but am having trouble with a formula to achieve this.

Can anyone help me with this? I am stumped.

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭

    You don't need a helper column. In your report, in the Filter section, you can use "has none of" or "is not one of".

    Then, after you select your second field in the filter, click the "and" link to the right. This will change it to OR.

    That means that if either column does not equal X (or whatever else you put in there) the row will show up on the report. Is that what you were going after?

    Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Michael Culley
    Michael Culley ✭✭✭✭✭

    Are you basically saying that if both columns don't match eachother you want it on a report?

  • Michael Culley
    Michael Culley ✭✭✭✭✭

    I believe the formula for this can get pretty long if you try to do one large formula. I would take the easy way out and create 3 checkbox helper columns, one called Has X, one called HAS TBD, and the other called HAS NA.

    Then for the X column, I would do:

    =IF(OR([Column1]@row = "X", [Column2]@row = "X"), 1, 0)

    Then repeat for other columns and change "X" to "TBD" and "N/A"

  • Michael Culley
    Michael Culley ✭✭✭✭✭

    Ryan's way is better. Disregard mine lol

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!