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!

Answers

  • Bassam Khalil
    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:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Matt D
    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
    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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!