Formula to avoid the count of duplicate text entries

Options

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

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!