Contains or IF formula

Lisa

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.

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

Michael Culley

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

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

Ryan's way is better. Disregard mine lol