Formula if multiple choices

Amy Bedard

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!

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

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.