Formula if multiple choices

I have a Countifs formula that I can get to work if a single item is selected from the dropdown but not if both options are selected.

I want to count:

1) Who's affected is Internal (works): =COUNTIFS({ProjectStatus}, "Open", {WhoAffected}, "Internal", {Live}, "Live Input")

2) Who's affected is External (works): =COUNTIFS({ProjectStatus}, "Open", {WhoAffected}, "External", {Live}, "Live Input")

3) Who's affected is BOTH Internal and External - NEED HELP PLEASE.

PS there are only two choices in that dropdown.

Thank you!

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@Amy Bedard

You'll want to use an OR statement with @cell and CONTAINS:

=COUNTIFS({ProjectStatus}, "Open", {WhoAffected}, OR(CONTAINS("Internal", @cell), CONTAINS("External", @cell)), {Live}, "Live Input")

https://help.smartsheet.com/function/or

https://help.smartsheet.com/function/contains

All comments

Jeff Reisman

@Amy Bedard

You'll want to use an OR statement with @cell and CONTAINS:

=COUNTIFS({ProjectStatus}, "Open", {WhoAffected}, OR(CONTAINS("Internal", @cell), CONTAINS("External", @cell)), {Live}, "Live Input")

https://help.smartsheet.com/function/or

https://help.smartsheet.com/function/contains

Amy Bedard

It worked. I used an AND instead of OR but was missing the CONTAINS portion. Appreciate the 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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.