COUNTIF and CONTAINS for a *multiselect* Contacts column?

Tim Morgan
Tim Morgan ✭✭
edited 02/21/23 in Formulas and Functions

Hi -- does anyone know if it's possible to use the combo COUNTIF & CONTAINS on a multiselect contacts column?

Use case: we have projects that involve multiple resources, which we've captured as a multiselect contacts column. I'd like to report on how many projects each resource has, which means I need a count of all of the instances (rows) their name appears in the multiselect contact column.

=COUNTIF({SCP - Func Dir Additional}, CONTAINS(Contact@row, @cell))

In the example above, the "Functional Director" column is the multiselect column, which will have multiple names entered in a single cell. The "Contact" column is the single contact that I'm using as my criteria.

Every time this incorrectly returns zero.

Notably, this COUNTIF/CONTAINS combo works great for other situations where the column being interrogated is a multiselect dropdown column. But I've been unable to get this to work with a multiselect contact column. Which is a shame, because this is exactly what I need to do.

Anyone else had this problem? (And ideas for a solution?..) Thanks!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!