8

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.

Industry
Department

Comments

Andree_Stara

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

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

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?

In reply to by DMurphy

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 !