Counting unique values in Column B against unique values in Column A

Amy Buckle

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

Bassam Khalil

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 Khalil

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:

Amy Buckle

Thank you @Bassam.M Khalil , that's perfect and has done exactly what I needed.

Bassam Khalil

@Amy Buckle

I am glad you found the solution and I will be happy to cooperate with you on more business in the future.