Updating a cell based on checkbox column and a multi-select column

Hi there,

I am new to SmartSheets and am struggling to use the CONTAINS formula. I need to allow for the option for [Trial Type] to be either Sales Strip, Sales Demo, or both Sales Strip and Sales Demo

The formula below doesn't work if both Sales Strip and Sales Demo are selected.

Do I need to use the CONTAINS function? Or is there another way to make this work if both are selected in the [Trial Type] column?

=IF(AND(Parent1 = 1, OR([Trial Type]1 = “Sales Strip”, [Trial Type]1 = “Sales Demo”)), TODAY(+10))

Thanks for your help

Find more posts tagged with

Accepted answers

Paul Newcome

@Gil Nash Having the OR inside of the AND statement the way it is in the original post is fine.

=IF(AND(OR(valueA, valueB), value1)

will work the same as

=IF(AND(value1, OR(valueA, valueB))

@Patrick Miller Try this...

=IF(AND(Parent1 = 1, OR(CONTAINS(“Sales Strip”, [Trial Type]1), CONTAINS(“Sales Demo”, [Trial Type]1))), TODAY(+10))

All comments

Gil Nash

Just as a general rule, the OR would need to be wrapped up under the AND (=IF(AND(OR(value1, value2), value2), TRUE, FALSE))

Having said that, I think this will work.

=IF(ISBLANK([Trial Type]@row), "", IF(Parent@row = 1, TODAY(+10), ""))

Paul Newcome

@Gil Nash Having the OR inside of the AND statement the way it is in the original post is fine.

=IF(AND(OR(valueA, valueB), value1)

will work the same as

=IF(AND(value1, OR(valueA, valueB))

@Patrick Miller Try this...

=IF(AND(Parent1 = 1, OR(CONTAINS(“Sales Strip”, [Trial Type]1), CONTAINS(“Sales Demo”, [Trial Type]1))), TODAY(+10))

Gil Nash

@Paul Newcome I've never been able to get that to parse correctly but I'm sure it was probably a typo on my end. Good to know though

Paul Newcome

@Gil Nash The easiest way to remember it is that the OR is considered a single "logical statement" for the AND function regardless of how many logical statements are inside of the OR function. Basically you write the OR function as you should, then drop it into any logical statement section of the AND function.

AND(logical_statement_1, logical_statement_2)

AND(logical_statement_1, OR(..........))

AND(logical_statement_1, logical_statement_2)

AND(OR(..........), logical_statement_2)

The OR could even be "logical_statement_2" of 3 if you happen to have that many within your AND. It is just making sure that you have the OR built properly and then dropping it into the AND as a single piece.

AND(logical_statement_1, logical_statement_2, logical_statement_3)

AND(logical_statement_1, OR(..........), logical_statement_3)

Patrick Miller

Thank you so much @Gil Nash and @Paul Newcome

Since I have additional options in the multi-select, this answer seemed to work the best

=IF(AND(Parent1 = 1, OR(CONTAINS(“Sales Strip”, [Trial Type]1), CONTAINS(“Sales Demo”, [Trial Type]1))), TODAY(+10))

I really appreciate your time

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.