What is the formula that I will need to track how many completed projects an employee has worked on
Here it the formula I was trying but I kept getting an #INVALID REF.
=COUNTIFS({DCIM Projects 20202021 Range 1}, ="Complete", AND({DCIM Projects 20202021 Range 2,="Susan ****"})) (Replaced employees last name with * for privacy)
We have a few projects that take multiple employees and some that it is individual. I am hoping that the formula will pull the information every time the employee is listed whether it is in a group or individual.
Any help you can offer would be appreciated
Answers

A few things:
 You don’t need the AND function, the COUNTIFS assumes AND with the multiple conditions.
 The last } is in the wrong place. It should come after “Range 2” in the second condition range.
 The equals sign on the condition isn’t needed either.
=COUNTIFS({DCIM Projects 20202021 Range 1}, "Complete", {DCIM Projects 20202021 Range 2}, “Susan ****")

Jason  I want to thank you for your help on this. The formula works now.

I just realized that this formula worked to find the projects that Sue worked by her self but it did not add the times she worked with another coworker.
There is an example of that in the screen shot I attached above where she worked on a project with Jesse ****.
Can you help me with what the formula would look like to accomplish that?

You would need a HAS function for that...
=COUNTIFS({DCIM Projects 20202021 Range 1}, "Complete", {DCIM Projects 20202021 Range 2}, HAS(@cell, "Susan ****"))

Thank you Paul for the help with this. I appreciate it.

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!