COUNT / DISTINCT / COLLECT / INDEX / MATCH
Hi there, I have a complicated formula I'm trying to complete and while it is returning the correct value in most cases, there seem to be some non-relevant data points being counted as well:
=COUNT(DISTINCT(COLLECT({Combined Name}, {Week#}, $[Wk 52]$28, {Role}, INDEX({Role}, MATCH($[Primary Column]@row, {Role}, 0)))))
I'm trying to count the distinct names in the combined name column of a referenced sheet with two conditions. First, the week number must match the number in my table heading (in this case 52). Second, the role in the referenced sheet must also match the role in my primary column.
It returned the correct value for two roles but also returned "1" for two roles that are not present in week 52. Am I missing a reference somewhere?
Best Answer
-
Hey @Nick Allgauer
I'll give it a try. Not seeing your sheet, we may have to tweak this a bit.
=COUNT(DISTINCT(COLLECT({Combined Name}, {Week#}, $[Wk 52]$28, {Role},[Primary Column]@row)))
Kelly
Answers
-
Hey @Nick Allgauer
I'll give it a try. Not seeing your sheet, we may have to tweak this a bit.
=COUNT(DISTINCT(COLLECT({Combined Name}, {Week#}, $[Wk 52]$28, {Role},[Primary Column]@row)))
Kelly
-
You got it! Perhaps I was overcomplicating it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!