Counting the number of unique values in a column

Amy Buckle

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.

Kelly Moore

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

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

Kelly Moore

Hey@Amy Buckleand 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

@KDMThank 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

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

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

Hmmm. The formula works for me.

Try this one.

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

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

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

Mark