Help with COUNT and DISTINCT function

Hi @jmoser

I'm able to replicate this when I have both numbers and text in the Primary Column (versus purely text). This may not be expected behaviour, so I'll check in with our Support team to see if they are aware of this issue.

In the meantime, what if you set up a "helper column" to bring over the values from the Primary into a Text/Number column, using a formula to ensure it's being read as a number, like so, =VALUE([Agreement #]@row)

Then the COUNT(DISTINCT should work on the helper column.

@jmoser I think it may have to do with the Primary Column. Decimals should not be a problem, but if any of these entries start with a 0, it will be read as text (since in order to display 0's Smartsheet has to add a ' mark in front).

Can you test with a helper column as suggested, and see if that works?

All comments

I just tried to replicate this and couldn't. How is the [Agreement #] column populated?

There is another sheet that has "move" automation setup so for any parent row with "Incorrect format" = true then move those entries over to this sheet. It looks to the "Incorrect helper" column to determine if the condition is met to check/uncheck "Incorrect format".

Does that help?

@Paul Newcome - does that additional info provide any details that might help?

You may want to consider reaching out to Support. Every time I have tried to count distinct number, it has worked properly for me.

Hi @jmoser

Then the COUNT(DISTINCT should work on the helper column.

@Genevieve P I tested numbers, but I must not have tested in the Primary Column. Good catch. Thanks!

@jmoser @Paul Newcome

This is expected! In our Help Article on the DISTINCT function, it notes that all values must be of the same data type in order for the function to calculate (see here).

So Jeff, it sounds like somewhere in that column you have cells with text, and other cells have numbers. The helper column idea above, which will translate all your content over to a value, should resolve your issue. Let me know if you need help with this!

Cheers,

Genevieve

@Genevieve P Thanks! I knew it had to be the same data type, but there wasn't any indication above that some might be text vs others being numbers. That's why I had initially asked how the [Agreement #] column was populated. I knew I should have dug a little further.

@Genevieve P - I don't have any text in that column however, I do have several entries that use decimal points (ie: 12345.6). Also, the [Agreement #] column is the primary column as well.

Would that cause the behavior of only showing 1?

Can you test with a helper column as suggested, and see if that works?

I did test with decimals and didn't have any problems, but again... I didn't test in the Primary Column.

@Genevieve P - I did the helper column and used =VALUE([Agreement #]@row), it counted the unique agreements!

I don't like using helper columns if I can help it but this one did the trick!

Thanks for your help and @Paul Newcome, thanks for lending a hand and verifying things.

Jeff

Hi Jeff,

I'm glad it worked for you!

I understand not wanting to add in extra columns to your sheet. You may be able to figure out which cell data was being read as an incorrect value type and manually change it in your Primary Column, but I think the Helper Column will be easiest for now as it will continually process the data as VALUES so you don't have to keep checking as new rows are created.