Count and Distinct Formula returns 1
Hi, I'd like to retrieve the total values excluding duplicates, this is the formula used: =COUNT(DISTINCT([Part Number]:[Part Number])) ; using the count formula only returns the total value of 3535, which is correct considering duplicated cells, I thought it was due to an error in the data format or something similar, so I expected an error in the grand total as well, but I have no clue.
The total value excluding duplicates should be 475.
Best regards,
Best Answer

Create a new column and put this in there:
=[Part Number]@row + ""
Then point your formula at that. Smartsheet can't do DISTINCT on a mix of text/numbers.
Answers

Create a new column and put this in there:
=[Part Number]@row + ""
Then point your formula at that. Smartsheet can't do DISTINCT on a mix of text/numbers.

@James Keuning great, now it works perfectly, thank you so much. What does adding a +"" accomplish? Is it like turning the cells into a 'string' format?

@lschek1 Yup, that's exactly what it does. Like STR() in SQL.
VALUE will take it the other direction, take numbers that Smartsheet sees as text, and interprets them as numbers.

@James Keuning Awesome, thank you so much for the valuable information. Have a nice day!
Help Article Resources
Categories
Check out the Formula Handbook template!