Counting the number of unique values in a column

I feel like this should be simple, but I can't work it out!

My smartsheet has 753 rows, but there are only 479 unique values in the column I'm looking at (which I discovered in Excel). How can I count this using a Smartsheet formula? I only want a number. No criteria. Help!

Thanks in advance.

Best Answer

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

    Hey Amy

    When using cross sheet references, we don't have to use the colon to designate a column range - the cross reference {range} is already doing that.

    =COUNT(DISTINCT({your reference sheet column})

    I wasn't sure if you had re-named your {Range 3} to {Range 3 [Group Name]}. If yes, kudos to you for using the good practice of renaming the generic smartsheet range number to a range name that mirrors the column name of that range.

    cheers

    Kelly

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Amy Buckle ,

    Just use COUNT. Try:

    =COUNT([column name]:[column name])

    Substitute the name of the column you want counted. It will count any row with a value in it.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Amy Buckle and Mark

    Building on Mark's formula, to get the number of Distinct values, add Distinct to the formula. As with Mark's formula, the column is a Text/Number column.

    =COUNT(DISTINCT([column name]:[column name]))

  • Amy Buckle
    Amy Buckle ✭✭✭

    @KDM Thank you, the DISTINCT part is what I needed! However I'm trying to get this number to show in a separate sheet, but the range part is confusing me. Are you able to show me where I'm going wrong?


    =COUNT(DISTINCT({Range 3 [Group Name]}:{Range 3 [Group Name]}))

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

    Hey Amy

    When using cross sheet references, we don't have to use the colon to designate a column range - the cross reference {range} is already doing that.

    =COUNT(DISTINCT({your reference sheet column})

    I wasn't sure if you had re-named your {Range 3} to {Range 3 [Group Name]}. If yes, kudos to you for using the good practice of renaming the generic smartsheet range number to a range name that mirrors the column name of that range.

    cheers

    Kelly

  • Amy Buckle
    Amy Buckle ✭✭✭
    edited 03/16/21

    Hi Kelly.

    That makes sense - but it's still not working.


    If I use =COUNT({Range 3 [Group Name]}) I get 759, which is correct.

    However, if I use =COUNT(DISTINCT({Range 3 [Group Name]})) I get 1. It should be 479.


    Any advice?

    Thank you

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hmmm. The formula works for me.

    Try this one.

    =COUNT(DISTINCT(COLLECT({Range 3 [Group Name]}, {Range 3 [Group Name]}, <>"")))

  • Amy Buckle
    Amy Buckle ✭✭✭

    I checked the data in the column I'm using, and found one cell with a number instead of text. Fixed it, and now your original formula works! Thanks for all of your help

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!