How to count unique values in different columns

BFuller
BFuller ✭✭✭
edited 01/31/23 in Formulas and Functions

Hello,

I am using smartsheet to run an internal promotion for some phone agents. The promotion is they have to refer 15 unique customers to our YouTube channel to earn the reward. So, I need to count the number of unique entries they have. For example, if an agent name matches my name@row, I want to count the number of entries when the following criteria are met:

  • ticketid does not match another ticketid in that column (so the ticket id has to be unique)
  • account name AND customer name do not match another account name AND customer name on a different row where my name@row is the agent (for example: if agent A and agent B both refer Customer A, both agents would get credit for that referral, but if Agent A refers Customer A twice, agent A only gets credit for one of those referrals).

Is there anyway to do this with a formula? Many thanks in advance!

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would suggest a helper column that joins the ticket id and customer name into a single string. Then you can use a COUNT/DISTINCT/COLLECT combo to get the count you are looking for.

    =COUNT(DISTINCT(COLLECT({Helper Column}, {Name Column}, @cell = Name@row)))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!