Examples of Count IF + Distinct
Hi SmartSheet Community!
I have many instances where I want to know the number of distinct values based on specific criteria. I think I need a combination of COUNTIF and DISTINCT. Can anyone provide some guidance?
Here is what I have so far but its not working:
=COUNTIF(DISTINCT(TransactionRole:TransactionRole, HAS(@cell, "CDE")))
I'd love to know the proper order for the operations and parens, as well as when it is better to use CONTAINs, HAS, or equal to. The particular field I have indicated as the criteria for the IF is a multi-select drop-down field so I can not just rely on 'equal to' because there are entries which include CDE but also include other multi-select values.
Let me know if more description would be helpful!
Thanks,
Ann
Answers
-
From my research you can't use Countif(Distinct together but you can use
Count(Distinct(Collect Together which should serve the same function so for your formula above would be
=COUNT(DISTINCT(COLLECT(Transactionrole:TransactionRole,Has(@cell,"CDE"))))
-
Thanks for your response, @Hollie Green. I wasn't sure how to use Collect.
Something is still not correct because it is only returning the a value of "1". I tried it with something other than CDE as well and it was the same. Ideas?
Ann
-
oh wow sorry I didn't put in the range to collect just put in the criteria range
=Count(Distinct(Collect(Transactionrole:TransactionRole,Transactionrole:TransactionRole,Has(@cell,"CDE"))))
-
Thanks, Hollie! That worked with a small tweak - the range to collect was the Entity Name. It returned a result of 26 which is in the expected range. Thank you for helping me understand the Distinct-Collect combo!
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!