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.

COLOMN A	COLOMN B	COLOMN C	COLOMN D
PART	SERIAL	LOCATION	RESULT
123456	BS12345	Z.2.J2	2
123456	BS12346	Z.2.J2	2
123456	BS12347	Z.2.J2	2
123456	BS12348	Z.2.J2	2
123456	BS12349	Z.2.J4	2
123456	BS12350	Z.2.J4	2
123456	BS12351	Z.2.J4	2
123456	BS12352	Z.2.J4	2

<tbody>
</tbody>

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.