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.

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]))

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