Count unique values using criteria

Justin Mauzy

I am trying to use the count formula with distinct and collect. Here is the code:

=COUNT(DISTINCT(COLLECT([Job Filled Date:]:[Job Filled Date:], ISDATE(@cell), [Zone:]:[Zone:], ISTEXT(@cell))))

Here is what I am trying to do:

I want to count the row if the text in the Zone: column and the date in the Job Filled Date: column are unique.
In the above example, I want to return a value of 5. Even though there are 14 entries, there are only 5 unique values.
The above formula only returns a value of 1.

I am sure that I am missing something.

Thanks for the help in advance.

Find more posts tagged with

Accepted answers

Maaik Meijerink

Hello @Justin Mauzy,

I added a new Column [Zone + Filled] with column formula, just to get the unique values based upon the two values together:

=IF(AND(ISTEXT([Zone:]@row); ISDATE([Job Filled Date:]@row)); [Zone:]@row + "-" + [Job Filled Date:]@row)

And then used the simple version of your formula getting the result of 5:

=COUNT(DISTINCT([Zone + Filled]:[Zone + Filled]))

All comments

Maaik Meijerink

Hello @Justin Mauzy,

I added a new Column [Zone + Filled] with column formula, just to get the unique values based upon the two values together:

=IF(AND(ISTEXT([Zone:]@row); ISDATE([Job Filled Date:]@row)); [Zone:]@row + "-" + [Job Filled Date:]@row)

And then used the simple version of your formula getting the result of 5:

=COUNT(DISTINCT([Zone + Filled]:[Zone + Filled]))

Justin Mauzy

This works perfectly. I had to change the ; to , and it worked. Thank you for the quick response.

Maaik Meijerink

https://community.smartsheet.com/discussion/comment/314779#Comment_314779

Yep, based in the Netherlands I must use ;

Glad I could help you out

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.