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

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!

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.