COUNTIFS and OR formula referencing multiple contact columns in another sheet

Miew

Hello,

I am trying to come up with a formula that would count the number of active projects for each member of our 6 teams. I am referencing data hosted in another sheet and I have multiple criterias for the COUNTIFS formula :

• The project has to have an active statut
• The name of the employee needs to appear in at least one of the 3 contacts column (so I imagine I need to use the OR function, but I didn't manage to make it work)
• Each project needs to be counted just once, even if the name appears in multiple contact columns.
• Each contact cell may contain more than one name. If the employee's name is one of them, this project should be counted.
• I need two formulas : one for the total of projects for each employees and one for the number of project per category (fruits or vegetables) per employee.

Here is a simplified example so we can work a model I would replicate in my real environment. The formulas I am looking for would generate the numbers in a Summary sheet.

Oh, one more thing : the formula needs to reference the "Employees's Name Column" as criteria. I have to apply this to six sections and more than 100 employees so I don't want to change the name in each formula.

So far I have only been able to get this formula working but it doesn't comply with all my requirements described above :

=COUNTIFS({Advisors}; HAS(@cell; [Employee's Name]@row); {Status}; "Active")

Thanks in advance for your help !

Miew

Wow, this is a game changer for me @Leibel S

Not only does it work like a charm, but I am able to reuse it many different ways in my project. Let's add that I would never ever have figure it out without you. Big thanks !

Leibel S

@Miew

In your data sheet do as follows:

1. Add column "LINE-ID" : Auto Number Column (unless you have one already, if yes use the column name in step 2)
2. Add column "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
3. Make sure agents, advisors, and coordinators columns are next to each other in your sheet.

Then your formula to count active through all 3 columns is the below (you will need to add the 2 cross sheet references bolded:

=COUNTIFS({ROW#}; HAS(INDEX({Agent-Coordinators cross sheet reference}; @cell); [Employee's Name]@row); {Status}; "Active")

Miew

Wow, this is a game changer for me @Leibel S

Not only does it work like a charm, but I am able to reuse it many different ways in my project. Let's add that I would never ever have figure it out without you. Big thanks !