Count(Distinct(Collect Help
Hi All,
I'm trying to count the number of 'distinct' participant to events based on the year and add that count to their org leaders.
In the table where I have the participant I've alredy added helper columns to identify their leaders at different lvl. My formula works fine as long as I point to the specific column where I expect the leader to appear, but it doesn't if instead I reference a group of column … I presume I'm doing something wrong on the 'collect' piece of the information …
here's the formula :
=COUNT(DISTINCT(COLLECT({Events Participant ID},{Event Year}, "23", {Event Leaders table}, [Leader ID]@row)))
If the {Event Leaders table} is a single column, then it works like a charm, but if that reference is a group of 4 column, it return an incorrect count
Best Answer
-
yeah, no, those columns are already 'helpers' … I ended up with a workaround :
I nested a series of IF statement for each of those column range, adding a reference to a 'level' column which will then point the formula to the correct column in the collect criterion …
=(IF(Level@row = "L8", COUNT(DISTINCT(COLLECT({Events Participant ID},{Event Year}, "23", {Event Leaders L8}, [Leader ID]@row))),IF(Level@row = "L7",COUNT(DISTINCT(COLLECT({Events Participant ID},{Event Year}, "23", {Event Leaders L7}, [Leader ID]@row))) and so on
it looks ugly as anything, but it get the job done.
Pretty sure I could at this point simplify the whole thing, but my brain is exhausted by this :D :D
Weird that there is no indication of that COLLECT limitation in the function description, it would have made my life a lot easier
Answers
-
essentially it seems that the COLLECT part of the formula is not working when 1 of the criterion range is not a single column, but 2 or more columns
-
I don't think you can have different sized ranges within a single function. If one range is a single column then you will need to reference a single column for all the other ranges as well, and if one range is 4 columns wide, then all ranges will need to be 4 columns wide.
One easy fix/workaround I can think of would be to add a helper column that JOINS those 4 columns into one cell within each row, then use that column as your range. Hope this helps!
-
yeah, no, those columns are already 'helpers' … I ended up with a workaround :
I nested a series of IF statement for each of those column range, adding a reference to a 'level' column which will then point the formula to the correct column in the collect criterion …
=(IF(Level@row = "L8", COUNT(DISTINCT(COLLECT({Events Participant ID},{Event Year}, "23", {Event Leaders L8}, [Leader ID]@row))),IF(Level@row = "L7",COUNT(DISTINCT(COLLECT({Events Participant ID},{Event Year}, "23", {Event Leaders L7}, [Leader ID]@row))) and so on
it looks ugly as anything, but it get the job done.
Pretty sure I could at this point simplify the whole thing, but my brain is exhausted by this :D :D
Weird that there is no indication of that COLLECT limitation in the function description, it would have made my life a lot easier
-
Ok yeah that's another workaround. I think adding another helper that joins the 4 cells into one would have been easier than that long nested IF statement, but if it works it works! Glad you figured it out. :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 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
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!