Multiple CONTAINS statements error

SS_user10294507 ✭✭✭
edited 04/04/23 in Formulas and Functions

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.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!