How to count individuals in multi-contacts separately?

afelo

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!

Kelly P.

@afelo

Try this:

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

Hope this helps!

Kelly P.

@afelo

Try this:

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

Hope this helps!

afelo

@Kelly P. It worked!!! Thank you!! :)

Kelly P.

@afelo

Happy to help!! 😊