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

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.

Answers

  • Werner Gerstacker
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!