Count Distinct Collect  Unique Values Several Columns  Help
Hello!
I am trying to get the unique number of countries that have a value greater than 0 from a set of columns, the formula works, if I just collect from one column, example below, I get what I want, but if I try to add Carlos's Colum or others to the formula, then it does not work or just returns 0 or 1.
=COUNT(DISTINCT(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], >0)))
The above is the one that works, if I try any of the below, it fails
=COUNT(DISTINCT(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], >0, Countries:Countries, [Carlos's Moves]:[Carlos's Moves], >0)))
=COUNT(DISTINCT(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], Countries:Countries, [Carlos's Moves]:[Carlos's Moves], >0)))
I am trying to show the total unique number of countries for which there are active moves greater than 0 in each column
Any ideas on what might be the correct formula?
Thanks!
Best Answer

You would need to insert a helper column and have the row's total on each. Then you would be able to reference the helper column and the country column.
Answers

I kept playing around and I am closer but now with the challenge that I think I might have duplicates
COUNTM(COLLECT(Countries:Countries, [Melissa's Moves]:[Melissa's Moves], >0)) + COUNTM(COLLECT(Countries:Countries, [Carlos's Moves]:[Carlos's Moves], >0))
It gives also what I need but I need to find a way to only count unique between the two

Lord have mercy.... I was able to do it with a report lol
If anyone knows how to get a formula to do this I would be interested in learning, thank you!

You would need to insert a helper column and have the row's total on each. Then you would be able to reference the helper column and the country column.

@Paul Newcome monuments should be raised in your honor! You are the best! I had the totals already and I didn't think of doing that.
THANK YOU!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!