Counting each of the values in a multi-contact field

I have a primary sheet where team members are dropped into a contact column if they're working on a project. So the Contact column for any given project row might have one contact or three contacts in it. Then we have a project status column.

Example... (Project Name Column | Team Contact Column | Status)

Project 1 | Kate

Project 2 | Kate, Aaron, Kara

On another sheet, I've built out a summary sheet for the various team contacts and the status of their projects. I have it setup to pull the following (screenshot) using this formula:

Screen Shot 2023-07-23 at 2.34.11 PM.png

=COUNTIFS({Business Leads Involved Staff}, PARENT(Metric@row), {Business Leads Status}, Metric@row)

HOWEVER, if I have two team members in the team contact column, it will ONLY count the first one and ignores the rest.

Is there a way via formula I can better pull out any additional team members so they get their credit for projects?

Appreciate any insights! Thank you!

Find more posts tagged with

Contacts

Formulas

Accepted answers

All comments

Paul Newcome

You would need to incorporate the HAS function.

=COUNTIFS({Business Leads Involved Staff}, HAS(PARENT(Metric@row), @cell), {Business Leads Status}, Metric@row)

KateP721

@Paul Newcome Thanks for such a quick response! I plugged the formula in but it doesn't seem to be pulling it correctly still. The other data remains correct (single counts) but still not pulling in where we have two people listed in a cell. I'm also now seeing that I misspoke and I apologize - it isn't pulling EITHER person in the cell. So when both Aaron and Kara are listed with a "developing" client, they both show "0" and should show "1". Greatly appreciate your help if you know of another tweak to add to the formula. Thank you!

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are getting ready to edit it? I use the above very frequently with no issues.

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.