Count Unique Values based on another value
Hi all,
Is it possible to count unique values from two separate columns in one formula? In the attached photo I am trying to count the amount of unique "AssociationName" for each unique "CompanyName"? Basically, I want to know how many AssociationName's for each CompanyName.
Does that make sense? Thank you!!!
Best Answer
-
Hi Brad, This formula should return the number of unique association names under a company name.
=COUNT(DISTINCT(COLLECT(AssociationName:AssociationName, CompanyName:CompanyName, = [insert company name])))
So you don't have to create a formula for each company, create a column of distinct company names using the method in the link below. Then reference the company name [=CompanyName@row] with the formula above to count the unique association names under each company.
Does this help? I'm curious to see if there's an easier way.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi Brad, This formula should return the number of unique association names under a company name.
=COUNT(DISTINCT(COLLECT(AssociationName:AssociationName, CompanyName:CompanyName, = [insert company name])))
So you don't have to create a formula for each company, create a column of distinct company names using the method in the link below. Then reference the company name [=CompanyName@row] with the formula above to count the unique association names under each company.
Does this help? I'm curious to see if there's an easier way.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Mark,
Thank you! Worked perfectly! Much appreciated!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!