Formula to avoid the count of duplicate text entries

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)

Screen Shot 2022-11-16 at 3.05.47 PM.png

Thank you,

Sidd

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.