Count number of unique countries per company

[D4rwin]

Hi,

I have a table of unique emails with corresponding company and country.
I want to have a column which counts the number of unique countries a company is active in.

Column D is where I want the count to be in. Can someone help me with a formula for this?

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]Email[/TD]
[TD]Company[/TD]
[TD]Country[/TD]
[TD]#Countries[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]A[/TD]
[TD]France[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]A[/TD]
[TD]Germany[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]A[/TD]
[TD]UK[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]B[/TD]
[TD]France[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]B[/TD]
[TD]France[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]C[/TD]
[TD]Germany[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD]C[/TD]
[TD]UK[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In D2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(1-($C$2:$C$8=""),IF($A$2:$A$8=$A2,MATCH($C$2:$C$8,$C$2:$C$8,0))),ROW($C$2:$C$8)-ROW($C$2)+1),1))

 

[wideboydixon]

Book1
	A	B	C	D
1	Email	Company	Country	#Countries
2	xxx	A	France	3
3	xxx	A	Germany	3
4	xxx	A	UK	3
5	xxx	B	France	1
6	xxx	B	France	1
7	xxx	C	Germany	2
8	xxx	C	UK	2
Sheet1

Cell Formulas
Range	Formula
D2	{=SUM(--(FREQUENCY(IF($B$2:$B$8=$B2,MATCH($C$2:$C$8,$C$2:$C$8,0)),ROW($C$2:$C$8)-ROW($C$2)+1)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[D4rwin]

Thanks for the responses. Should have specified that I am allergic to array formulas.
Any other nifty way using sumproduct perhaps?

 

[wideboydixon]

Take some Benadryl. Good luck.

 

[Aladin Akyurek]

Thanks for the responses. Should have specified that I am allergic to array formulas.
Any other nifty way using sumproduct perhaps?

This might make you change your mind: https://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html