Count unique values with a condition

[malcolmk]

I have an excel sheet that I use to keep track of sales from my sales team. One of the columns contains the CUSTOMER NAME and another column contains the SALESPERSON that has that customer.

WHAT I NEED TO SEE IS HOW MANY UNIQUE CUSTOMERS EACH SALESPERSON HAS SOLD TO.

A salesperson may make several sales to the same customer but it should only count it once. So although a salesperson may make 90-100 sales per month, it may only be to 30-40 unique customers!

I hope I have made myself clearly understood.

Thanks

 

[Aladin Akyurek]

I have an excel sheet that I use to keep track of sales from my sales team. One of the columns contains the CUSTOMER NAME and another column contains the SALESPERSON that has that customer.

WHAT I NEED TO SEE IS HOW MANY UNIQUE CUSTOMERS EACH SALESPERSON HAS SOLD TO.

A salesperson may make several sales to the same customer but it should only count it once. So although a salesperson may make 90-100 sales per month, it may only be to 30-40 unique customers!

I hope I have made myself clearly understood.

Thanks

CondDistinctCountWithFrequencyAndCountDiff.xls
	A	B	C	D	E	F
1					With FREQUENCY	With COUNTDIFF
2	Customer Name	Salesperson		Salesperson	# Distinct Cust	# Distinct Cust
3	c1	karl		karl	3	3
4	c3	jon		jon	2	2
5	c2	karl		damon	3	3
6	c6	damon		dan	3	3
7	c5	dan		brian	2	2
8	c7	damon
9	c8	brian
10	c8	dan
11	c9	dan
12	c3	karl
13	c8	jon
14	c6	brian
15	c5	damon
Sheet1

E3:

=SUM(IF(FREQUENCY(IF($A$3:$A$15<>"",IF($B$3:$B$15=D3,MATCH($A$3:$A$15,$A$3:$A$15,0))),ROW($A$3:$A$15)-ROW($A$3)+1),1))

which is confirmed with control+shift+enter, not just with enter, and copied down.

Invoking a formula with CountDiff of the morefunc.xll add-in...

F3:

=COUNTDIFF(IF($B$3:$B$15=D3,IF($A$3:$A$15<>"",$A$3:$A$15)),FALSE,FALSE)

which also must be confirmed with control+shift+enter, not just with enter, and copied down.

 

[malcolmk]

Thank you.

Much appreciated