How to count the total number of projects assigned to a trainer

I have a list of projects and multiple trainers can be assigned to each. I am trying to summarize in a metric, and then dashboard, how many In Progress projects 1 trainer is working on. I have tried every variation of the FIND and CONTAINS formula and continue to get UNPARSEABLE error.

I want to add a metric to count ongoing project additions from 1 sheet, then create a bar chart that has how many projects Mary, Tim, Mohammad, Greg, and Alisha are all assigned to

So in the below I want to show that out of the In Progress Projects (total 3) Mary has 1, Tim has 2, Mohammad has 2, Greg has 2

Project 1 (Status: In Progress): Mary, Tim, Mohammad

Project 2 (Status: Complete): Greg, Mary

Project 3 (Status: In Progress): Tim, Alisha

Project 4 (Status: In Progress): Greg, Mohammad

Notes: all 3 these contacts are indicated in 1 column "Trainers Assigned"' so in line 1 Mary, Tim and Mohammad are contacts selected in the column "Trainers Assigned"

Below is the formula I found from other posts but it does not work and gives UNPARESEABLE error

=COUNTIFS({Stage}, "In Progress", FIND("Mary",[Trainer],{Trainer}>0))

including a screenshot of the column names

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!