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,

Find more posts tagged with

Sheets

Formulas

Accepted answers

James Keuning

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.

All comments

James Keuning

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.

lschek1

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

James Keuning

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

lschek1

@James Keuning Awesome, thank you so much for the valuable information. Have a nice day!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.