Counting unique values in Column B against unique values in Column A
I want a formula that will enable me to count how many unique Groups per Account Manager, eg AM 2 has entered 4 groups, but only 2 are unique. I've used a COUNT formula to see how many times each Account Manager appears in Column A, and a COUNT(DISTINCT) formula to count the unique Groups in Column B, but how do I do this per Account Manager as well?
Account Manager | Group Name
AM 1 | Capeside High School
AM 2 | Rydell High School
AM 2 | Rydell High School
AM 3 | McKinley High School
AM 2 | Greendale Community College
AM 1 | Hogwarts School
AM 2 | Rydell High School
What I want to achieve from this data would look like this:
Account Manager | Unique Groups
AM 1 | 2
AM 2 | 2
AM 3 | 1
Best Answer
-
Hi @Amy Buckle
Hope you are fine, please do the following,
1- create a helper column with column format formula TEXT = MID(n@row, 7, 100)
2- create a helper column with column format formula AM = =LEFT(n@row, 4)
3- create a helper column with a column for criteria contain all your criteria
4- count the unique Groups per Account Manager using this formula
=COUNT(DISTINCT(COLLECT(TEXT:TEXT, AM:AM, Criteria@row)))
the following screenshot shows the results:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hi @Amy Buckle
Hope you are fine, please do the following,
1- create a helper column with column format formula TEXT = MID(n@row, 7, 100)
2- create a helper column with column format formula AM = =LEFT(n@row, 4)
3- create a helper column with a column for criteria contain all your criteria
4- count the unique Groups per Account Manager using this formula
=COUNT(DISTINCT(COLLECT(TEXT:TEXT, AM:AM, Criteria@row)))
the following screenshot shows the results:
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Thank you @Bassam.M Khalil , that's perfect and has done exactly what I needed.
-
I am glad you found the solution and I will be happy to cooperate with you on more business in the future.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!