Count Distinct Values that only appear multiple times

I have a formula that follows this structure

=count(distinct(collect(range, crit_range1, crit1, etc)))

that allows me to count the number of unique entries that meet a set of criteria. Now I'm trying to modify that formula that allows me to count the number of repeat entries that meet a set of criteria.

In the original formula, I'd get a result of 4 (assuming this list met all the criteria). With the new formula that I'm asking for help with, I'd get a result of 2 (John and James show up multiple times on the list).

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hello @Jeremy Center

In my approach, I needed a helper column (checkbox) to accomplish this. You can slide the checkbox column to the far right and hide it. I would make this a column formula

In the helper column

=IF(COUNTIFS([your john doe column]:[your john doe column], [your john doe column]@row) > 1, 1)

The above is finding any entry that occurs in your name column more than once.

Then bring this helper column into your Count/Distinct/Collect where that helper column has a 1

=COUNT(DISTINCT(COLLECT([your john doe column]:[your john doe column], [your john doe column]:[your john doe column], <>"", [helper checkbox]:[helper checkbox],1)))

The DISTINCT will only count the checkbox columns once.

does this work for you?

Kelly

All comments

Kelly Moore

Hello @Jeremy Center

In my approach, I needed a helper column (checkbox) to accomplish this. You can slide the checkbox column to the far right and hide it. I would make this a column formula

In the helper column

=IF(COUNTIFS([your john doe column]:[your john doe column], [your john doe column]@row) > 1, 1)

The above is finding any entry that occurs in your name column more than once.

Then bring this helper column into your Count/Distinct/Collect where that helper column has a 1

=COUNT(DISTINCT(COLLECT([your john doe column]:[your john doe column], [your john doe column]:[your john doe column], <>"", [helper checkbox]:[helper checkbox],1)))

The DISTINCT will only count the checkbox columns once.

does this work for you?

Kelly

Jeremy Center

https://community.smartsheet.com/discussion/comment/318776#Comment_318776

I love clever solutions! Thank you for your help.

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.