COUNTIFS incorrectly counting number of times a contact appears in Assigned To column

Matt D

I am trying to track the number of tasks each employee is assigned to. In my Assigned To column, some cells have one contact assigned to a task and others multiple contacts. I am currently using the following function:

=COUNTIFS([Assigned To]:[Assigned To], "John Smith", [Status]:[Status], <>"Complete")

The function returns a number, but it is incorrect. For example, I know one of my contacts has 7 tasks assigned to them, but the function is returning the number 2. I have also tried using COUNTIFS with HAS but got the same results:

=COUNTIFS([Assigned To]:[Assigned To], HAS([Assigned To]:[Assigned To], "John Smith"), [Status]:[Status], <>"Complete")

It seems that the function is simply not finding all of the results. Any feedback is greatly appreciated!

Bassam Khalil

Hi @Matt D

Hope you are fine, please try the following formula:

=COUNTIFS([Assigned To]:[Assigned To], HAS(@cell, "John Smith"), Status:Status,
NOT(@cell = "Complete"))

The following screenshot show shows the result:

Matt D

Hi @Bassam Khalil

Hope you are well. Thank you for the swift response. However, I tried the function you provided and it returned the same results as before. I do not think the problem is the Status column reference, as COUNTIFS returns the same incorrect value when I remove the Status column reference. I think the issue resides within the COUNTIFS function.

Bassam Khalil

@Matt D

if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it