# Contains or IF formula

Options
✭✭✭

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.

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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"

• ✭✭✭✭✭
Options

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!