Unique countif on a multi-select use case

Caden Karras

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

Paul Newcome

In your metrics sheet, set it up so that you have a column for the name, a multi-select dropdown column, and then the count column (text/number).

In the multi-select dropdown, enter this:

=JOIN(COLLECT({Source Sheet Project Column}, {Source Sheet Name Column], [Name Column]@row), CHAR(10))

This will pull all cells together from the projects column where that person is listed in the name column, but... Since it is in a multi-select dropdown column, it will not duplicate project names.

Now all we have to to is count how many selections are made in this multi-select dropdown which Smartsheet just so happens to have a function specifically for accomplishing...

=COUNTM([Multi-Select Dropdown]@row)

Paul Newcome

In your metrics sheet, set it up so that you have a column for the name, a multi-select dropdown column, and then the count column (text/number).

In the multi-select dropdown, enter this:

=JOIN(COLLECT({Source Sheet Project Column}, {Source Sheet Name Column], [Name Column]@row), CHAR(10))

This will pull all cells together from the projects column where that person is listed in the name column, but... Since it is in a multi-select dropdown column, it will not duplicate project names.

Now all we have to to is count how many selections are made in this multi-select dropdown which Smartsheet just so happens to have a function specifically for accomplishing...

=COUNTM([Multi-Select Dropdown]@row)