If column does not contain string return N/A
Hi,
I've created a formula to return me the average "word" that appears when the Title column contains the string "1. Establish Baseline for Web Performance":
=IF(AND(COUNTIFS(Rating:Rating, "Extreme-4", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance") >= COUNTIFS(Rating:Rating, "High-3", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance"), COUNTIFS(Rating:Rating, "Extreme-4", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance") >= COUNTIFS(Rating:Rating, "Medium-2", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance"), COUNTIFS(Rating:Rating, "Extreme-4", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance") >= COUNTIFS(Rating:Rating, "Low-1", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance")), "Extreme-4", IF(AND(COUNTIF(Rating:Rating, "High-3") >= COUNTIFS(Rating:Rating, "Extreme-4", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance"), COUNTIFS(Rating:Rating, "High-3", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance") >= COUNTIFS(Rating:Rating, "Medium-2", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance"), COUNTIFS(Rating:Rating, "High-3", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance") >= COUNTIFS(Rating:Rating, "Low-1", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance")), "High-3", IF(AND(COUNTIF(Rating:Rating, "Medium-2") >= COUNTIFS(Rating:Rating, "Extreme-4", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance"), COUNTIFS(Rating:Rating, "Medium-2", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance") >= COUNTIFS(Rating:Rating, "High-3", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance"), COUNTIFS(Rating:Rating, "Medium-2", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance") >= COUNTIFS(Rating:Rating, "Low-1", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance")), "Medium-2", IF(AND(COUNTIF(Rating:Rating, "Low-1") >= COUNTIFS(Rating:Rating, "Extreme-4", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance"), COUNTIFS(Rating:Rating, "Low-1", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance") >= COUNTIFS(Rating:Rating, "High-3", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance"), COUNTIFS(Rating:Rating, "Low-1", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance") >= COUNTIFS(Rating:Rating, "Medium-2", Status:Status, "Open", Title:Title, "1. Establish Baseline for Web Performance")), "Low-1"))))
However, if the title column does not contain the string "1. Establish Baseline for Web Performance", how can I get the result to return to me N/A?
E.g in here:
Answers
-
Try this:
=IF(NOT(CONTAINS("1. Establish Baseline for Web Performance", Title@row)), "N/A", original_formula)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks @Paul Newcome. I also have a status column that has a drop down saying "open", "close" or "on hold". How can I also apply this for the ones that are ONLY "open"?
I tried to do this and it didn't work: =IF(NOT(CONTAINS("1. Establish Baseline for Web Performance", Title@row)), (NOT(CONTAINS("Open", Status@row)), "N/A")
-
@Ann11 I'm not sure I follow. Are you wanting to apply the "N/A" to rows that don't contain "1. Establish........" and the status is "open"?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome yes that's correct
-
=IF(AND(NOT(CONTAINS("1. Establish Baseline for Web Performance", Title@row)), NOT(CONTAINS("Open", Status@row))), "N/A")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome do you know why it doesn't work when I try to apply the whole columns like this?:
=IF(AND(NOT(CONTAINS("1. Establish Baseline for Web Performance", Title:Title)), NOT(CONTAINS("Open", Status:Status))), "N/A")
-
@Ann11 Exactly what are you wanting to accomplish?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome this question was based on this https://community.smartsheet.com/discussion/comment/333320#Comment_333320
So I'm trying to get "Summary" of words. I know I can do this via the summary feature in Smartsheet or do this in a report/graph but I need to do it this way so I can link cells to other sheets.
For example:
The summary of the "strings" I'm trying to gather is coming up as extreme if that "string" is not found in title column or has "open" in the status column which is false:
If the title doesn't exist and status is not open, I want it to return N/A
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!