Count Distinct Values that only appear multiple times

Jeremy Center

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).

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

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.