Trying to count multiple people in a cell

BryanHeft

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.

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

DMurphy

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

Paul Newcome

@Deanna Vandermeer While you cannot reference multiple sheets within a function, you can link multiple functions together that each reference a different sheet.

=COUNTM({Sheet A Multi-Select})

would give you the count for Sheet A

=COUNTM({Sheet B Multi-Select})

would give you the count for Sheet B

=COUNTM({Sheet A Multi-Select}) + COUNTM({Sheet B Multi-Select})

would combine the count for both.

This is the only way to reference multiple sheets within a single cell because it breaks the different sheets into separate functions.

Andrée Starå

@Paul Newcome Haha!

Yes, of course!

I just had a . . . moment:

https://media.giphy.com/media/aVtdz7iNVPI1W/giphy.gif

Paul Newcome

@Andrée Starå Haha. I have plenty of those moments.

Andrée Starå

@Paul Newcome Watch out! There's a new contender! 🤣

Deanna Vandermeer

@Andrée Starå - I will submit an enhancement requests. I have tried the sheet summary formulas but unfortunately even there I am finding an issue with my multi-contact column type formulas returning two additional counts to the total. My assumption, based from quite literally pretending to set up a filter and counting each and every choice I see in the dropdown to set a filter. What I am finding when I do this is, and why I am assuming it is returning 2 over the number it should, is because it is somehow including "Blank" and "Current User". Odd I know but I can find no other explanation why I am always getting a number that is two over each and every time. So, @Paul Newcome workaround seems to be a fix (for now). His solution for that was to simply add "-2" to the end of the formula, which I have done.

However, now I am trying to figure out a good way to either run a metrics sheets to return not only the distinct count of the person (resource) AND the total number of projects they are assigned to for each individual project sheet. I did try the COUNTM + COUNTM solution that Paul mentioned above but it is currently not returning the correct number either. So I'll just keep working at it until I come up with a workaround or you or Paul have other suggestions. You are both so very helpful and I want to thank you both for the help thus far.

Paul Newcome

@Deanna Vandermeer Happy to help!!

You said you tried COUNTM + COUNTM but it is not returning the correct results. My suggestion is to start by separating the formulas and troubleshooting each one individually.

If you also have "Blank" and "Current User" in the other sheets, it may be as simple as subtracting 2 from each, then adding those together.

=(COUNTM(......) - 2) + (COUNTM(......) - 2)

Deanna Vandermeer

Thank you I do already have my formulas written individually and they work. I always start off by doing that first. I am doing your trick of -2 but the combining just simply does not want to return a valid number at all but I'll keep getting after it until I crack this. I appreciate all of your wonderful suggestions. I did come across one weird anomaly in one of my results. Each and every multi-contact column so far is returning exactly 2 over so the -2 solution is working but I have one column that is returning exactly 10 more than it should, weird I know, but I have yet to figure this one out and am at my wits end with this. I have never experienced so much trouble with formulas. Anyway, thank you again for being so helpful.

Andrée Starå

@Deanna Vandermeer

Excellent!

Strange that it adds almost two each time. Have you reported it to the Support Team?

Smartsheet Support Team

I'm always happy to help!

Paul Newcome

@Deanna Vandermeer At this point, I would also suggest contacting support. I can understand (somewhat) the +2 from "Blank" and "Current User", but the +10... That has me a touch befuddled (unless it is counting the 10 empty rows at the bottom of the sheet, but then why wouldn't it do that on all sheets??).

Please let us know if they are able to help.

Deanna Vandermeer

@Andrée Starå & @Paul Newcome -- I have already reported this to my account customer support rep and he has reported this but I will follow up and also report via the link Andree included (thank you). Yes, Paul my first assumption was that it was counting 10 additional empty cells in that column but in reality there are empty cells in that particular column so this has be befuddled as well. I will keep you both in the loop in case I, or support, ever find out the cause. It seems to me that in addition to the wonderful COUNTM that we currently have we need a DISTINCTM too, maybe they will work on this. In the meantime, I have moved on to plan B and am working on a metric sheet and have abandoned the Sheet Summary option for these particular counts. But I sure do appreciate you both very much and will update as I learn or figure out more. Thank you both so much.

Andrée Starå

@Deanna Vandermeer

You're more than welcome!

Have a fantastic weekend!