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.

Find more posts tagged with

Human Resources

Formulas

Reports

Services

Comments

Andrée Starå

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

Daniel Stein

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

BryanHeft

That works! Fantastic! Thank you both for the assistance.

Andrée Starå

Happy to help!

Best,

Andrée

Dale Murphy

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?

Jeremy Warren

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)

gatessagar

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 !

ahoward

Just the formula that I was looking for to count contacts in a cell, thank you!

JennS

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!

brhea110891

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?

Paul Newcome

@JennS_ & @brhea110891

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.

Deanna Vandermeer

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?

Paul Newcome

@Deanna Vandermeer

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.

Deanna Vandermeer

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.

Andrée Starå

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.