Counting each of the values in a multi-contact field


I have a primary sheet where team members are dropped into a contact column if they're working on a project. So the Contact column for any given project row might have one contact or three contacts in it. Then we have a project status column.

Example... (Project Name Column | Team Contact Column | Status)

Project 1 | Kate

Project 2 | Kate, Aaron, Kara

On another sheet, I've built out a summary sheet for the various team contacts and the status of their projects. I have it setup to pull the following (screenshot) using this formula:

=COUNTIFS({Business Leads Involved Staff}, PARENT(Metric@row), {Business Leads Status}, Metric@row)

HOWEVER, if I have two team members in the team contact column, it will ONLY count the first one and ignores the rest.

Is there a way via formula I can better pull out any additional team members so they get their credit for projects?

Appreciate any insights! Thank you!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!