Counting unique values in a data table contingent on another field

[ollieotis]

Hello,

I'm trying to figure out which function to nest within the below to allow for counting of unique values in one column, contingent on a value in different column.

SUM(IF(FREQUENCY(MATCH(D7:D595,D7:D595,0),MATCH(D7:D595,D7:D595,0))>0,1))

Essentially, I'm trying to come up with a sumif like function, only to count unique values.

Example, column A contains the primary key, or Project code, column B contains the client code, and column C contains the salesperson assigned, with any particular client or salesperson having multiple project codes assigned. Is there a way to revise the above function to count number of unique client codes per salesperson?

Thanks in advance!

 

[Weazel]

if i'm understanding correctly maybe something like...


Excel 2012

	A	B	C	D	E
1	primary	client code	salesperson		joe
2	a123	Z5555	joe		3
3	b321	x96	mike
4	a123	a777	joe
5	b321	x96	joe
6	c251	Z5555	joe

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula E2 {=SUM(IF(FREQUENCY(IF($C$2:$C$10<>"",IF($C$2:$C$10=$E$1,MATCH($B$2:$B$10,$B$2:$B$10,0))),ROW($B$2:$B$10)-ROW($B$2)+1),1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[ollieotis]

Brilliant! Thank you!