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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    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


    the following screenshot shows the results:

    PMP Certified

    [email protected]

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!