Using IF and OR together

I am very new to smartsheet and I am trying to condition a cell to be "Yes" or "No" based on items noted in a specific column

=IF(CONTAINS("ItemA", Order:Order), "Yes", "No")

...but I also want to include OR IF "Item B" OR IF "item C" is there

The don't all have to be there to be "Yes" just at least one of them

Thank you in advance!

Find more posts tagged with

Formulas

Accepted answers

All comments

Jeff Reisman

@Sam Walsh

Question 1: Is the string you're looking for just part of the possible value in a cell in the Order column, or is the whole cell equal to ItemA, or ItemB, or ItemC?

Question 2: Are you checking to see if ANY rows in the Order column contain those values, or if just the particular row you're on does?

If you want to check the whole column for the presence of one or more cells that equal any of your three values, you can do this without CONTAINS, but you'll need a COUNTIF in there:

=IF(COUNTIF(Order:Order, OR(@cell = "ItemA", @cell = "ItemB", @cell = "ItemC")) > 1, "Yes", "No")

If you want to check the whole column for the presence of one or more cells that contain any of your three values, you can use CONTAINS:

=IF(OR(CONTAINS("ItemA", Order:Order), CONTAINS("ItemB", Order:Order), CONTAINS("ItemC", Order:Order)), "Yes", "No")

If you just want to check the Order cell on the particular row for your values:

=IF(OR(Order@row = "ItemA", Order@row = "ItemB", Order@row = "ItemC"), "Yes", "No")

If you just want to check if the Order cell on the particular row contains one or more of your values:

=IF(CONTAINS(Order@row = "ItemA", Order@row = "ItemB", Order@row = "ItemC"), "Yes", "No")

Sam Walsh

Question 1: Is the string you're looking for just part of the possible value in a cell in the Order column, or is the whole cell equal to ItemA, or ItemB, or ItemC?

A, B or C would be individual cells in the Order column by themselves

Question 2: Are you checking to see if ANY rows in the Order column contain those values, or if just the particular row you're on does?

Yes, any of the rows (individual cells) in the order column contain A, B or C would be "Yes"-- if none are contained in the order column then "No"

Sam Walsh

If you want to check the whole column for the presence of one or more cells that contain any of your three values, you can use CONTAINS:

=IF(OR(CONTAINS("ItemA", Order:Order, CONTAINS("ItemB", Order:Order), CONTAINS("ItemC", Order:Order)), "Yes", "No")

I believe this is what I need or close to

Sam Walsh

From the "Yes" results in my sheet-- I want to generate a report that pulls PDFs tied to the "Yes" results

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320147#Comment_320147

Since "A, B or C would be individual cells in the Order column by themselves," either of the first two formulas would work. The first one does it faster with less system overhead.

=IF(COUNTIF(Order:Order, OR(@cell = "ItemA", @cell = "ItemB", @cell = "ItemC")) > 1, "Yes", "No")

The formula with CONTAINS in there three times with have to evaluate the entire column three times, vs once for the first formula. In a big sheet that can cause slowness/reduced response time.

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.