Formula for "Allow Multiple Contacts per Cell"

Options

I have been tasked with creating a dashboard with some metrics on it. Below is an example of what I am looking to have created.

On one of my smartsheet columns is an "Assigned to" section. There are 3 people in that list.

Bob, Jan, and Jill.

Bob has 1 row assigned to him.

Jan has 1 row assigned to her.

Jill has 1 row assigned to her.

This is the formula I used to show me how many rows are assigned to each person. =COUNTIF({Test example range 1}, [Primary Column]@row).

The formula does the calculation and everyone has 1. Perfect.

The problem is there is a 4th row. On the 4th row, it is assigned to Bob, Jan, AND Jill.

So, my goal is to find a formula that would show that they each have 2 rows assigned to them, not 1.

Any help would be greatly appreciated.

• ✭✭✭✭✭✭
Options

Hello @WesMcNally

Multiselect columns need the functions HAS, CONTAINS or FIND. Try this

=COUNTIFS({Test example range 1}, HAS(@cell,[Primary Column]@row))

Does this work for you?

Kelly

• ✭✭✭✭✭✭
Options

Hello @WesMcNally

Multiselect columns need the functions HAS, CONTAINS or FIND. Try this

=COUNTIFS({Test example range 1}, HAS(@cell,[Primary Column]@row))

Does this work for you?

Kelly

• Options

That did the trick! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!