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:

Screen Shot 2022-06-17 at 3.58.24 pm.png

Find more posts tagged with

Sheets

Formulas

Support

Project Management

Accepted answers

All comments

Paul Newcome

Try this:

=IF(NOT(CONTAINS("1. Establish Baseline for Web Performance", Title@row)), "N/A", original_formula)

Ann11

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")

Paul Newcome

@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"?

Ann11

@Paul Newcome yes that's correct

Paul Newcome

=IF(AND(NOT(CONTAINS("1. Establish Baseline for Web Performance", Title@row)), NOT(CONTAINS("Open", Status@row))), "N/A")

Ann11

@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")

Paul Newcome

@Ann11 Exactly what are you wanting to accomplish?

Ann11

@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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.