Unique countif on a multi-select use case

I have a sheet where we are trying to do a count of project for each person.


Here is the information:

The sheet lists all the services/applications that a team supports.

There are potentially multiple team members assigned to support a service/application.

We also have projects that may or may not span across the services/applications.

So, the team members are a multi-select column and the projects are a multi-select column.

Both multi-select columns are associated with the static list of services/applications.

We would like to create a dashboard that reports how many services/applications a team member is assigned as well as how many projects the team member is contributing to/assigned.



I am trying to count the number of projects each individual is on. The trick is that we have to not count the duplicate projects as we go down through the rows.

Does anyone have insight into this problem?

*This is a simple test sheet I have made to show what I am trying to do. The actual sheet the formula will be used on will be much larger.

Thanks

Screen Shot 2021-08-11 at 9.45.58 AM.png


Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!