COUNTIFS Calculation with Multiple Selections in Cell
I have a task tracking sheet with a column called "Assigned To" that is of type "contact list". In my sheet summary I have functions that count the total number of tasks assigned to each individual team member. However, there are several tasks that are assigned to more than one person. Those tasks assigned to more than one person are not getting counted as part of the individuals' total number of assigned tasks and subsquently shown on a dashboard. What is the best way/function to accomplish this?
My formula for only accounting for one individual's total number of assigned tasks looks like this: =COUNTIF([Assigned To]:[Assigned To], "Erica")........and works great.
Other team members include Mike, Chris, and Todd. If a task is assigned to say Erica and Todd, I want to count that task in the total number of tasks assigned to both individuals. I've tried the following but get the UNPARSEABLE error.
My current formula attempting to account for multiple people assigned to one task looks like this: =COUNTIF([Assigned To]:[Assigned To], "Chris", AND ((OR "Mike", "Ian", "Erica", "Todd", "Karl")))........and does not work. ☹️
Any help would greatly be appreciated!
Best Answer
-
Paul Newcome
✭✭✭✭✭
CONTAINS does not work with Contact columns, but FIND does.
=COUNTIF([Assigned To]:[Assigned To], FIND("Chris", @cell) > 0)
This will eliminate the need for a helper column.
Answers
@Michael Murphy
Try =COUNTIF([Assigned To]:[Assigned To], CONTAINS("chris", @cell)) this would still count each person even if there are multiple people assigned.
IF Chris and Mike were assigned to a task then both would have another task added to their count.
Unfortunately this did not work. It didn't give me any error, but returned a result of "0" instances.
@Michael Murphy
you are right i didn't see that it was a contact column. for it to work you can add a helper column that joins all the names in a text string and run your countif with contains on that.
Okay, I'll give this a shot and see what happens. Thanks.
So is it fair to say the CONTAINS function doesn't work when operating on cells in a contact column? Is this the case for most functions (that they don't work when used in a contact column)?
To try and understand what the issue is, I tried the following as well, where the "Assigned To 1" column is a contact column and "Assigned To 2" is a multiple drop down list using only first names. As you can see, the formula for "Assigned To 1" returns the value of "1" because there is only one entry where the individual is listed by himself.
However, when using simple first names in a multiple selection drop down list column type, Smartsheet doesn't like the formula.
So is it fair to say, the only way to count multiple entries in a cell, is if the column is of the text/number type? I've only just begun using Smartsheet, so I am not a proficient user, but this seems odd. I say this because Smartsheet has Gantt Chart sheets where resources can be applied. It is common practice to assign multiple resources to one task. So if I'm using a Gantt chart and want to see how many tasks employee "a" is involved in to track resource utilization, would I have to use a "helper" column in that instance as well?
In order for the count to work with a contact cell you wold need to bring the Join function in to the formula like this
=COUNTIF([Assigned To 1]:[Assigned To 1], CONTAINS("Chris", JOIN(@cell)))
this would allow you to eliminate the need for a helper column.
I am also having trouble with this. I have multiple events and I am trying to get the total of tasks per team member. Some tasks have multiple team members working on it. I am trying to create a report but when I enter the formulas it will only count individual cells, not the tasks assigned to multiple team members. How can I get this to work?
@Ashley Mayo If you are evaluating a Contact type column, you will need to incorporate the FIND function like so...
=COUNTIF([Assigned To]:[Assigned To], FIND("Chris", @cell) > 0)