Counting individuals supported by specific customer removing repeats

akarlsson

Hi,

I am trying to sum the number of individuals that are supported by a customer. There are several opportunities that support a single customer and within these opportunities the same individuals may be supporting the customer. How do I calculate the unique number of individuals who support a single customer. The individual column is based on a dropdown list.

Courtney S.

Good afternoon! I assumed you will use a separate sheet for the customer-based summaries. Here is the solution I came up with.

Sheet 1 - ProjectSheet:

Sheet 2 - SummarySheet:

The first formula column in the Summary Sheet, "Multiselect column with JOIN" needs to be a Dropdown List column type, with "Allow multiple values per cell" turned on. The formula uses cross sheet references to collect together all the Supporting Staff answers for the customer name on that row: =JOIN(COLLECT({ProjectSheet Staff Supporting Column}, {ProjectSheet Customer Column}, [Customer name]@row), CHAR(10))

The second formula column, "Count of the JOIN list" is much simpler, it just uses the COUNTM formula to count the number of people in the first column: =COUNTM([Multiselect column with JOIN]@row)

Reference pages:

COLLECT Function | Smartsheet Learning Center

JOIN Function | Smartsheet Learning Center

CHAR Function | Smartsheet Learning Center

COUNTM Function | Smartsheet Learning Center

Create efficient formulas with @cell and @row | Smartsheet Learning Center

Create cross sheet references | Smartsheet Learning Center