Count unique values based on a different column, both columns include multi select

Stephanie Lapera

Hello,

I have a partner column (multi-select) and a district column (multi-select). In the sheet summary I am trying to determine the number of unique districts each partner is working with. There are over 100 districts so I need a generic formula that doesn't require me to find a district by name. I prefer to do this in the sheet summary but can add an additional column or more if needed.

Example below. The sheet summary is what I am trying to see as my output. Thanks in advance.

John Drugan

Hi @Stephanie Lapera - this sheet summary formula (Edit: almost) worked for me (you may want to change the ranges) - =COUNTM(COLLECT(District1:District4, Partner1:Partner4, HAS(@cell, "P1")))

If anyone can help add the "distinct" feature in... that will make P3 show "3" correctly instead of "4". Sorry ran out of time.

-JD

Stephanie Lapera

Thanks @John Drugan that has gotten me a great start. I am still having a hard time adding the distinct to show "3" instead of "4" for P3.

I've also tried creating a separate column and using JOIN, DISTINCT, COLLECT + CHAR to pull the names into a single cell + on separate lines and hoping I can count from the new single cell but I still can't get it to pull distinct and in some cases it is joining the district names into one.

I will keep trying and thanks for your help in getting started.

Genevieve P.

Hi @Stephanie Lapera

What you said in the last comment is what I would suggest!

I would use a formula in a new Multi-Select column to collect together all the distinct multi-select values associated with each Partner. Then you can use the COUNTM on this helper column to identify how many you pulled together.

How did you write the JOIN formula?

Try something like this:

=JOIN(DISTINCT(COLLECT(District:District, Partner:Partner, "P1")), CHAR(10))

If you have a column listing the unique Partners, you could use that as your criteria instead:

=JOIN(DISTINCT(COLLECT(District:District, Partner:Partner, [Partner Helper]@row)), CHAR(10))

Let me know if this works for you!

Cheers,

Genevieve

Leibel S

@Genevieve P. @Stephanie Lapera

The formula needs to use the HAS function to work, and does not need DISTINCT because the Multi Value field does it for you.

As @Genevieve P. wrote create a Multi Value column. Add the below column Formula:

=JOIN(COLLECT(District:District, Partner:Partner, HAS(@cell,[Partner Helper]@row)), CHAR(10))

Then your final count is:

=COUNTM([multivalue column]@row)

fix column names as needed.

Genevieve P.

Thanks @Leibel Shuchat, good catch!