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 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

Find more posts tagged with

Accepted answers

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:

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.