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 !
