How to count individuals in multi-contacts separately?

I'm working on a formula for project allocation. I'm extracting my information from two columns in another sheet, master tracker. The columns from the master tracker are Status and Project Leads.

In the sheet for Project Allocation, I'm using the following:

The Columns are the Status and Person 1, Person 2, Person 3, etc.

The Rows are In Progress, Not Started, and Completed.

I was partially successful in gathering the number of projects each project lead was working on, until I realized some of the rows in the Project Leads column have two contacts listed and not just one. Here is the formula I was using:

=COUNTIFS({Master Tracker Range 2}, "Person 1", {Master Tracker Range 1}, "In Progress")

I'm not sure if I need to be using a different formula, or just modify the current one.

Can someone please help me figure this out?

Thank you!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!