Counting a Unique Name in a contact list that has more then one name in it

I have a contact column called "Team".

A task will have multiple unique names in the "Team" cell that is working that task.

I want to count how many task people are working on at the parent level for the task.

I have a helper filed that list the level for a task called "Level"

I found this formula on another post but I am getting the wrong count back

=COUNTIFS({Level - SOP}, 1, {Team - SOP}, FIND(Name@row) > 0)

I have the names and the formula on a "Count Sheet" that is refencing my "Project Sheet"

In the example below Manie is working on 28 task that are a "Level" 1 and Kellie is working on 1 but the formula is brining back a count of 68 for everyone

Any help would be greatly apricated


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Sean Corcoran

    Hope you are fine, please try the following formula in the parent level:


    the following screenshot shows the result:

    PMP Certified

    ☑️ 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"

  • Sean Corcoran
    Sean Corcoran ✭✭✭✭

    @Bassam Khalil

    Thanks for the reply but this won't work for what I am trying to do.

    The names are not in a column as you shown them. The multiple names are in one cell per task in a contact field that you can add more then one contact in the cell. That is the problem I am having, If I only have one name in the cell I can use this formula:

    =COUNTIFS({Level - SOP}, 1, {Lead - SOP}, =Name@row)

    This is a screen shot of the "Project Sheet"

    The team is the same for each Task so the first thing I want to do is only count where the task is a Parent or Leve 1. So I need some kind of a formula that you can use the @row on a summary sheet to look for a name in a contact field in the Project Sheet when there is more then one name in contact field

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Insert a multi-select dropdown column and use this formula...

    =IF(COUNT(CHILDREN(Team@row)) <> 0, SUBSTITUTE(JOIN(CHILDREN(Team@row), CHAR(10)), ", ", CHAR(10)))

    This will generate a list of each name one time (duplicates filtered out).

  • Sean Corcoran
    Sean Corcoran ✭✭✭✭

    @Paul Newcome

    Thanks, you formula might come in handy in the future for other stuff but below is what I was looking for.

    =COUNTIFS({Level - SOP}, 1, {Team - SOP}, FIND(Name@row, @cell) > 0)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    My apologies. I saw the mention of "unique name" and thought that's what you were going for.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!