Formula to avoid the count of duplicate text entries

SiddV47

Hello,

In one of my sheets, I have the formula to count the number of partners (existing and new). But I need to avoid the count of multiple entries for the partner names. See the image below:

As you may notice the Partner - "GTA" was selected in two different rows and therefore it is counted twice in the # Partners column. Similarly, "Precision Health Coalition-Global (NGO in Karachi)" is counted twice in the # Partners column as it is being selected in two separate rows.

Please provide a workaround to avoid the count for these duplicate text entries in the # Partners column.

The current column formula for the # Partners Column is -

=COUNTM([Partners (existing)]@row) + COUNTM([#New Partners 2022]@row)

Thank you,

Sidd

Genevieve P.

Hi @SiddV47

What I would do here is join together all of your cells in that multi-select column into one multi-select cell. This will automatically merge duplicate selections as 1 selection, so you can COUNTM just the new, merged cell!

=JOIN([Partners (existing)]:[Partners (existing)], CHAR(10))

Cheers,

Genevieve