Count unique entries among two columns


I am looking for a way to create a report or a column on an existing smartsheet to count employees by unique supervisor name

Thanks for any ideas!


Best Answer


  • EvermoreCoffee

    Hi @Rebecca Menning,

    I took the data you've provided and was able to get something similar to the outcome of what you are looking for, on a sheet:

    I am not sure if there is a way to list the [Unique Employees] as separate rows, though we can use the CHAR(10) combined with a JOIN() formula, followed with wrapping the values in that column, to get a similar layout. Here are the formulas I used for each column:

    [Supervisor Helper] (Used to assist the DISTINCT() and CONTAINS() formulas)

    =JOIN([Supervisor 1]@row:[Supervisor 2]@row, ",")

    [Count of Unique Employees]

    =COUNT(DISTINCT(COLLECT(Employee:Employee, [Supervisor Helper]:[Supervisor Helper], CONTAINS(Supervisors@row, @cell))))

    [Unique Employees]

    =JOIN(DISTINCT(COLLECT(Employee:Employee, [Supervisor Helper]:[Supervisor Helper], CONTAINS(Supervisors@row, @cell))), CHAR(10))

    The [Supervisors] column is just manual name entry for any supervisors.

    Hope this helps!

  • Rebecca Menning
    Answer ✓

    Thank you for the idea! We will certainly be experimenting

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!