Count Distinct Values that only appear multiple times

Options

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

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!