COUNTIFS and OR formula referencing multiple contact columns in another sheet
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 !
Best Answer

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 !
Answers

In your data sheet do as follows:
 Add column "LINEID" : Auto Number Column (unless you have one already, if yes use the column name in step 2)
 Add column "ROW#" : Column Formula: =MATCH([LINEID]@row, [LINEID]:[LINEID], 0)
 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({AgentCoordinators cross sheet reference}; @cell); [Employee's Name]@row); {Status}; "Active")

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 !
Help Article Resources
Categories
Check out the Formula Handbook template!