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

Sean Corcoran

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

Hi @Sean Corcoran

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

=COUNT(DISTINCT(CHILDREN())) 

the following screenshot shows the result:

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

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

@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

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