Which formula do I need if I want to count issues assigned to 2 people that are counted for each ind

Hi everyone,

I am fairly new to Smartsheet and this is my first question in this community!

I have an Issue Tracker where issues can be assigned to multiple people. On one of the charts on my dashboard I am showing how many Issues are assigned to each individual. I have a formula that counts if an issue is assigned to 1 individual. For example: =COUNTIFS({Issue Type}, "ISSUE", {Assigned To}, "Tom Smith")

I the occasion where issue/task is assigned to 2 or more people I need a formula that would count that issue and add it to all individuals assigned, and I can't figure it out. So for example if 1 issues is assigned to Tom and Donna, I need this same issue to count for Tom AND Donna. Does anyone have an idea how to solve this?

This were two of my failed attempts:

=COUNTIFS({Issue Type}, "ISSUE", {Assigned To}, "Tom Smith") + COUNTIFS({Issue Type}, "ISSUE", {Assigned To}, "*Tom Smith*")

=COUNTIFS({Issue Type}, "ISSUE", {Assigned To}, "Tom Smith") + COUNTIFS({Issue Type}, "ISSUE", {Assigned To}, "*Tom Smith*,*")

Find more posts tagged with

Formulas

Dashboards

Countif

Accepted answers

All comments

Mark.poole

@LeaVol

I just have a couple of questions to better build you the proper solution. First would be have you attempted using the OR function. Secondly could we get a screen shot of what your working with for some more context. YOu can clean this of any sensitive information. Third. When you are assigning something to multiple people. IS it from a mult-select drop down?

LeaVol

Hi @Mark.poole, thank you for getting back to me! I apologize for my late reply.

To answer your questions

No, I have not tried using the OR function.
Yes, when assigning to multiple people, it is a multi-select dropdown: this is the sheet that I want to use then for the graph:

Let me know if you need other visuals or if I need to explain the case better.

Thank you for your help in advance!

Mark.poole

Hi @LeaVol,

No worries about timing.

Try using the CONTAINS or HAS functions for each person.

=COUNTIFS({Issue Type}, "ISSUE", {Assigned To}, HAS(@cell, "Tom Smith"))

=COUNTIFS({Issue Type}, "ISSUE", {Assigned To}, CONTAINS(@cell, "Tom Smith"))

The difference between the two is HAS looks for exact spelling. while CONTAINS just looks for the string.

ex.

Andy

And

HAS(@cell, "And") would only count And.

CONTAINS(@cell, "And") would count both Andy and And

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.