Help with COUNT and DISTINCT function

jmo

Hi team - I have a column called Agreement # that has various agreement numbers in them but also many duplicates. I'm trying to get a count of unique numbers but when I combine the COUNT and DISTINCT functions - it keeps coming up with 1.

The Agreement # column looks like this (example):

Is there a problem because there are blanks between the parent row with a number in it?

How would I make the COUNT and DISTINCT funtions to work and disregard blank fields, if that's the problem?

Thanks,

Jeff

Genevieve P.

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.

Genevieve P.

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

Paul Newcome

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

jmo

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?

jmo

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

Paul Newcome

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

Genevieve P.

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.

Paul Newcome

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

Genevieve P.

@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

Paul Newcome

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

jmo

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

Genevieve P.

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

Paul Newcome

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

jmo

@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

Genevieve P.

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.

Paul Newcome

@jmoser I tend to shove all of my helper columns off to the right of the sheet and then Hide them. It helps keep the sheet from getting too cluttered. Otherwise I personally love helper columns for some of the more complex processes.

If you do decide to go the route of locating the text value, I suggest using a helper column for that too (at least until you are able to get it fixed). A text number column with something as basic as

=IF(ISTEXT([Primary Column]@row), 1)

will flag any rows that have a text value instead of a numerical value, and dragfilling the formula down a column is much faster than checking each cell individually.

@Genevieve P Thanks for the help!