COUNTIF - combination of colomns

[beman]

Hi,

I checked the forum but couldn't find anything that worked for me.

The problem I have is getting excel to count only the unique combination Part and Location, in the example below it should return the number 2 (colomn D) as there are 2 unique locations for the combination.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]COLOMN A[/TD]
[TD]COLOMN B[/TD]
[TD]COLOMN C[/TD]
[TD]COLOMN D[/TD]
[/TR]
[TR]
[TD]PART[/TD]
[TD]SERIAL[/TD]
[TD]LOCATION[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]BS12345[/TD]
[TD]Z.2.J2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]BS12346[/TD]
[TD]Z.2.J2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]BS12347[/TD]
[TD]Z.2.J2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]BS12348[/TD]
[TD]Z.2.J2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]BS12349[/TD]
[TD]Z.2.J4[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]BS12350[/TD]
[TD]Z.2.J4[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]BS12351[/TD]
[TD]Z.2.J4[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]BS12352[/TD]
[TD]Z.2.J4[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]


Hope someones knows how to get this done with light/simple formula.

Thanks in advance for the help!

 

[Eric W]

Try this in D3:

=SUM(SIGN(FREQUENCY(IF($A$3:$A$10=A3,MATCH($C$3:$C$10,$C$3:$C$10,0)),ROW($C$3:$C$10)-ROW($C$3)+1)))

confirm with Control+Shift+Enter after putting the formula in the formula bar.