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

Find more posts tagged with

Accepted answers

All comments

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.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.