Trying to count multiple people in a cell
I have a column set as Contact List and the Allow Multiple Contacts per Cell field is checked. I am using COUNTIF statements to count how many people are assigned to the tasks for a dashboard.
I am using the formula: =COUNTIF({Project List Range 3}, Assigned1)
Where Project List Range 3 is the Column in my master sheet that is the Contact List Field with assigned project members and Assigned1 is the name of an individual.
If the individual I am searching for is the only name in the field, it gets counted correctly. If there are multiple people assigned in the field, it does not count any of the names.
Comments
-
Hi Bryan,
It doesn't work at the moment with multiple contacts. Smartsheet is working on a solution.
A workaround would be to use a formula with the Find function. Here is another post in the community and I'll get back to you when I find it.
I hope this helps you!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Bryan,
COUNTIF always looks at the complete cell value. If you want to look at part of a cell value, you can use FIND as Andree mentioned.
For your use case, this formula should work: =COUNTIF({Project List Range 3}, FIND(Assigned1, @cell) > 0)
Best regards,
Daniel
-
That works! Fantastic! Thank you both for the assistance.
-
Happy to help!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
How about determining the FTE equivalent assigned to a task? If there is more than one resource, the allocation needs to be multiplied by the number of resources to get full work effort. How does one do that?
-
I had a similar need, and finally came up with this formula which counts the number of people in an assigned to field. It's counting commas in the field, so this might not work in some circumstances but was good enough for my needs.
=IF([Assigned To]@row <> "", LEN([Assigned To]@row) - LEN(SUBSTITUTE([Assigned To]@row, ",", "")) + 1, 0)
-
Hi Guys, i tried the formula "COUNTIF({Project List Range 3}, FIND(Assigned1, @cell) > 0) " but it didnt seem to work. Then i tried to see if there is some error in the Find function, apparently when i select a "Reference sheet" and select the entire column, the "Find" function doesnt seem to work, it just works when i select a cell and returns something. Is there any other crack for this ?
Because if the Find Returns an error, the countif returns an error and i have to use Countifs (to see which task is green/ yellow / red etc. ) So its quite confusing. I could crack the one that has single contacts but when its multiple , the countifs doesnt work anymore.
Shall be great if you can let me know. Thanks !
-
Just the formula that I was looking for to count contacts in a cell, thank you!
-
I got this formula working for a single value lookup, but what if I want to look up two or more? So, for example, I want a COUNTIF the column shows Value1 or Value2.
Here is my current formula that is working, but I can't figure out how to add another find to:
=COUNTIFS({RANGE1}, FIND("VALUE1", @cell) > 0, {RANGE2}, NOT(OR(@cell = "Complete", @cell = "Cancelled")))
Appreciate any help!
-
So, after reading this thread, I'm understanding that there is no formula or option to get a count of UNIQUE contacts in a sheet. I am tracking various projects where one person may be assigned to several projects, but I just want the total number of resources assigned. Suggestions?
-
I suggest creating a new thread for each of your questions as your solutions will vary from the solution for this original post.
Feel free to @mention me in your post, and I will see if we can help find you both an appropriate solution.
-
I too am struggling with finding a formula to return a count of UNIQUE contacts in a multi-select contact column type. My current formula is as follows: =COUNTM(DISTINCT([Project Lead]:[Project Lead]))
The count that consistently is being returned is 2 higher than the count should be. My thought is that perhaps it is somehow also pulling in "Blank" and "Current User" but can't be certain and have had absolutely no success in finding a viable workaround. This is very important and I hope someone has a solution or that Smartsheet will work on creating one. We too have multiple projects where the same person may be assigned to more than one project and we need to have an accurate total count on the number of individuals overall who are considered "Project Leads".
Please, please will someone help us find a workaround for this issue?
Smartsheet Overachievers Alumni
-
If your formula of
=COUNTM(DISTINCT([Project Lead]:[Project Lead]))
is ALWAYS two over, then you could just subtract two from the formula.
=COUNTM(DISTINCT([Project Lead]:[Project Lead])) - 2
As for tracking distinct lead vs assist, I use two columns. I use a single select column for the Lead and then a second multi-select column for the assists. This makes it a lot easier (for me) when I have to pull further metrics.
-
Thank you, this helpful. I do actually have 4 separate columns depending on the project assignment (Lead, Core Team, Extended Team, Manager). I have a metric sheet set up and I am also trying to pull cross-sheet formulas to count each but would love to combine into a single formula instead of one for each individual project sheet. Do you happen to know if there is a way to create a multi-sheet cross-sheet formula to pull in a count? Thanks.
Smartsheet Overachievers Alumni
-
Hi Deanna,
Unfortunately, it's not possible at the moment to use cross-sheet formulas across multiple sheets, but it's an excellent idea!
Please submit an Enhancement Request when you have a moment to have your vote added
As a possible workaround, depending on your use case and needs could be to use the Sheet Summary and Sheet Summary Report.
Would that be an option?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!