On Sheet Summary: Count # of distinct values in a column

Lng98881

Hello, I have a Smartsheet where I'm trying to count distinct values in a column. Currently I've tried this but get #UNPARSEABLE.

=Count(Distinct(Collect(Prj_ID:Prj_ID,Prj_ID:Prj_ID,Prj_ID@row)))

Where Prj_ID is the column where I want to to count distinct Project IDs.

I'm hoping to get help from someone who's done something similar.

I've also tried:

=Count(Distinct(Prj_ID:Prj_ID))

but in that case I get the value as '1' which isn't correct.

Werner Gerstacker

I have used the same formula, i.e. =Count(Distinct(Prj_ID:Prj_ID)), with mixed results.

It seems to work more reliably on columns that hold text than on columns that hold numbers. That said, I am getting proper results on some number columns and just a '1' on others.

Werner Gerstacker

Hi @Lng98881,

while working with the Distinct function some more over the last few days I figured out the all entries in the column (area?) you want to look at need to be of the same type, i.e. all numbers or all text.

If you mix text and numbers in the same column using the function will result in receiving a 1 as output.