Count unique values using criteria
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.
Best Answer
-
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]))
Answers
-
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]))
-
This works perfectly. I had to change the ; to , and it worked. Thank you for the quick response.
-
Yep, based in the Netherlands I must use ;
Glad I could help you out
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!