Multiple Select Contact Column/Sheet Summary

I'm looking for a formula that I can use in Sheet Summary to identify individuals assigned to the same task via a multiple select contact column. The below is what I currently use to track daily tasks for individuals.

Target Tasks

=COUNTIFS([Due Date]:[Due Date], =TODAY(), [Critical Ops.]:[Critical Ops.], ="Yes", Personnel:Personnel, "Employee 1")

Completed Tasks

=COUNTIFS([Completion Date]:[Completion Date], =TODAY(), Status:Status, ="Yes", Personnel:Personnel, "Employee 1")

So, as stated, the issue is this wouldn't count tasks with multiple contacts in the same cell, and I'd prefer to keep it as a contact column if possible so i can then begin creating Workflows for each employee assigned.

Find more posts tagged with

Sheets

Formulas

Accepted answers

Leibel S

Use the has function. See below:

=COUNTIFS([Due Date]:[Due Date], =TODAY(), [Critical Ops.]:[Critical Ops.], ="Yes", Personnel:Personnel, HAS(@cell, "Employee 1"))

=COUNTIFS([Completion Date]:[Completion Date], =TODAY(), Status:Status, ="Yes", Personnel:Personnel, HAS(@cell, "Employee 1"))

All comments

Leibel S

Use the has function. See below:

=COUNTIFS([Due Date]:[Due Date], =TODAY(), [Critical Ops.]:[Critical Ops.], ="Yes", Personnel:Personnel, HAS(@cell, "Employee 1"))

=COUNTIFS([Completion Date]:[Completion Date], =TODAY(), Status:Status, ="Yes", Personnel:Personnel, HAS(@cell, "Employee 1"))

Brandon Mitchell

Thank you, it worked like a charm, I must have been putting the HAS function in the wrong place in my original tests.

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.