Replacement for "Distinct"

Steven Butler

My store numbers are both numeric and alpha numeric

I am using a COUNT(DISTINCT) formula for the "Store Number" range from the Master sheet

but it returns a "1" is there another formula I should be using?

Paul Newcome

I sure have. The data has to be all the same data type either alpha OR numeric.

Insert a helper column that has a column formula of

=[Store Number]@row + ""

to convert everything to text values. Then use this new column in your cross sheet reference.

Mike Wilday

Does your cross-sheet range include the entire column? That would be the first thing I check. Distinct shouldn't care whether it's alpha or numeric it should count the distinct number.

Steven Butler

It does include the entire column.

Mike Wilday

Hmmm. @Andrée Starå @Paul Newcome Ever seen this behavior before?

Paul Newcome

I sure have. The data has to be all the same data type either alpha OR numeric.

Insert a helper column that has a column formula of

=[Store Number]@row + ""

to convert everything to text values. Then use this new column in your cross sheet reference.

Paul Newcome

Here is a link to the DISTINCT function's help page. The note at the bottom indicates that all values must be of the same value type or you may end up with an incorrect count.

https://help.smartsheet.com/function/distinct

Steven Butler

Thank you for the formula! All good now!

Paul Newcome

Happy to help. 👍️

Mike Wilday

Thanks Paul. I totally missed that note on the Distinct Function page. I actually looked there too.

😑

Paul Newcome

@Mike Wilday No worries. the only reason I remember is because I see it popping up here in the Community at least once every couple of weeks.