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
-
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
-
Are you basically saying that if both columns don't match eachother you want it on a report?
-
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"
-
Ryan's way is better. Disregard mine lol
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!