Trying to count multiple people in a cell

BryanHeft
BryanHeft
edited 12/09/19 in Formulas and Functions

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.

«1

Comments

  • Andrée Starå
    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

    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.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • Dale Murphy
    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?

  • 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
    edited 04/02/19

    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!

  • JennS
    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
    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
    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
    Deanna Vandermeer Overachievers Alumni

    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

  • Paul Newcome
    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
    Deanna Vandermeer Overachievers Alumni

    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

  • Andrée Starå
    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

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!