Multiple CONTAINS statements error

SS_user10294507

I am trying to count multiple criteria using a COUNTIFS formula that also has a CONTAINS statement, which works fine until I add a 2nd CONTAINS statement. The detailed data lives in a different sheet.

Fields are Org = Org Code (one of about 30); WS = Workstream (Sales, Finance, HR, etc.); Roles = UAT Tester, SME, Power User, etc.).

Roles has always been a multi-select field, so it could have UAT Tester and SME as values. Workstream was a single-select dropdown when my working formula was written. It has recently been changed to a multi-select allowing Finance and Sales to be selected. This is what broke the formula. If it has Finance and Sales (multiple workstreams) the formula fails.

I tried adding a second CONTAINS statement, which returns a INCORRECT ARGUMENT SET error.

Working formula (if only 1 workstream present):

=COUNTIFS({Org}, "AOC:CLAR:CLAR", {WS}, ("Finance"), {Roles}, CONTAINS("UAT Tester", @cell))

Broken formula (when multiple workstreams present):

=COUNTIFS({Org}, "AOC:CLAR:CLAR", {WS}, CONTAINS("Finance"), {Roles}, CONTAINS("UAT Tester", @cell))

Can you help me to get a second CONTAINS statement added to get this to properly count? Thank you in advance.

Paul Newcome

Try using the same syntax as the other CONTAINS.

CONTAINS("specific text", @cell)

Paul Newcome

Try using the same syntax as the other CONTAINS.

CONTAINS("specific text", @cell)

SS_user10294507

Paul, Absolutely brilliant. Simple fix but I couldn't have done it without you. Many thanks!

Paul Newcome

Happy to help. 👍️