How to count unique values in different columns

BFuller

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!

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