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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!