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*,*")
Answers
-
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?
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
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!
-
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"))
or
=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
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!