Sumproduct to count unique values only

[YasserKhalil]

Hello everyone
I have a helper column in column F

=IF(SUMPRODUCT(($B$5:$B5=$B5)*($E$5:$E5=$E5))=1,1,"")

This helper column to count unique values only in I4:I7
It is required to count nationality and skipping duplicated values in Column B
Here's the attachment to illustrate the issue

I need to get rid of the helper column and use only one formula without the need of helper column. Is it possible?

 

[Nebu]

Hi:

Find the attached.

=SUM(--(FREQUENCY(IF(($E$5:$E$24=H4),COUNTIF($B$5:$B$24,"<"&$B$5:$B$24),""),COUNTIF($B$5:$B$24,"<"&$B$5:$B$24))>0))

Array Formula.

Thanks

 

[YasserKhalil]

Mr. Nebu
Thanks a lot for this wonderful solution. You are great Excel Ninja
Best Regards

 

[bines53]

Hi,

Another two, without CSE,

=SUM(--(FREQUENCY(INDEX(($E$5:$E$24=H4),0)*$B$5:$B$24,INDEX(($E$5:$E$24=H4),0)*$B$5:$B$24)>0))-1

=SUM(INDEX(($E$5:$E$24=H4)/COUNTIFS($E$5:$E$24,$E$5:$E$24,$B$5:$B$24,$B$5:$B$24),0))

David

 

[bosco_yip]

Another non-CSE option :

=SUMPRODUCT((E$5:E$24=H4)/COUNTIFS(E$5:E$24,E$5:E$24,B$5:B$24,B$5:B$24))

Regards
Bosco

 

[YasserKhalil]

Thank you David & Bosco for these great contributions
Best Regards