COUNTIFS Calculation with Multiple Selections in Cell

Options

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!