How to count...

[#1]

Hi Everyone,

Here's a simple one for you:

The way that my data is set up is that for every deal, all the products sold in that deal is listed for each rep that was involved in the deal.  Thus, for a deal with 2 products and 3 reps (3x2) there are 6 rows of data.  However, if I want to count the number of reps on a deal, it will count the number of instances of a name, and the answer will be 6, not 3.

What expression can I use to only count unique instances of a name in a deal?

Thanks for your help

Deal ID	Products Sold	Sales Rep
Deal 1	Bike	John
Deal 1	Bike	James
Deal 1	Bike	Mary
Deal 1	Pump	John
Deal 1	Pump	James
Deal 1	Pump	Mary
Deal 2	Tires	John
Deal 2	Tires	James
Deal 2	Tires	Mary
Deal 2	Helmet	John
Deal 2	Helmet	James
Deal 2	Helmet	Mary

[#2]

Hi

PFA hope it will help you

Nirmal

[#3]

Hi

PFA,,, Hope this will reach your acheivment

[MK_QSL · Accepted solution]

Hope this is what you are looking for...

[#5]

Hi Nirmal,

Thanks for the response.  Based on your suggestion, I would need to split out the Deal ID, but in reality, I cannot split it out.  The example I posted is simply for demonstration.  If I cannot split out the Deal ID, is there another way?

Deal	ID	Products Sold	Sales Rep
Deal	1	Bike	James
Deal	1	Bike	John
Deal	1	Bike	Mary
Deal	1	Pump	James
Deal	1	Pump	John
Deal	1	Pump	Mary
Deal	2	Helmet	James
Deal	2	Helmet	John
Deal	2	Helmet	Mary
Deal	2	Tires	James
Deal	2	Tires	John
Deal	2	Tires	Mary

[#6]

Thanks Manish.