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

    =COUNT(DISTINCT(COLLECT(TEXT:TEXT, AM:AM, Criteria@row)))

    the following screenshot shows the results:


    PMP Certified

    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

  • 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

    =COUNT(DISTINCT(COLLECT(TEXT:TEXT, AM:AM, Criteria@row)))

    the following screenshot shows the results:


    PMP Certified

    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"

  • Amy Buckle
    Amy Buckle ✭✭✭

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

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

    PMP Certified

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!