Count Distinct Collect Returning 1
I am trying to populate a metrics sheet from data contained is a source sheet. In this scenario I am trying to get distinct values that fall between two dates.
This is the formula that I am trying to get to work, but it is currently returning 1.
=COUNT(DISTINCT(COLLECT({PAS Assigned}, {Date Logged} >= $[Start Date]@row, {Date Logged} <= $[End Date]@row)))
As a test I tried to simplify using something other than dates.
=COUNT(DISTINCT(COLLECT({PAS Assigned}, {Rating} >= 1)))
In each case the formula is returning 1, when this is not the case. If I create a count(distinct(collect())) formula in the source sheet it works correctly.
Am I doing something wrong?
Thank you,
Best Answer
-
Paul is meaning commas between the {range} and the criteria >=$[Start Date]@row
So in your example above:
=COUNT(DISTINCT(COLLECT({Week Number}, {Rating} = 1)))
There's no comma after {Rating}
Try this:
=COUNT(DISTINCT(COLLECT({Week Number}, {Rating}, = 1)))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Just a quick update...
This gets the distinct count
=COUNT(DISTINCT({Week Number}))
As soon as I add COLLECT into the equation I get the result of 1
=COUNT(DISTINCT(COLLECT({Week Number})))
=COUNT(DISTINCT(COLLECT({Week Number}, {Rating} = 1)))
-
None of the formulas should be outputting a number. There should be some kind of error as there are syntax issues. You need to insert commas between each range and its criteria.
-
I believe I do... See attached.
-
Paul is meaning commas between the {range} and the criteria >=$[Start Date]@row
So in your example above:
=COUNT(DISTINCT(COLLECT({Week Number}, {Rating} = 1)))
There's no comma after {Rating}
Try this:
=COUNT(DISTINCT(COLLECT({Week Number}, {Rating}, = 1)))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Ah.... Now I understand and your right. Now I'm returning actual values.
Thank you so much!
-
No problem! I'm glad we could help 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!