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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!