Counting if a unique identifier corresponds to another unique identifier more than once

[alekos7]

I'm trying to determine how to count the number of times a text value in ID1 matches up with a text value in ID2, but the problem is that the values aren't necessarily unique. See below for some sample data:

COLUMN A (ID1): SLS.123, SLS.123, SLS.123, SLS.234, SLS.123, SLS.123, SLS.299, SLS.123, SLS.299, SLS.333

COLUMN B (ID2): GPS.111, GPS.222, GPS222, GPS.222, GPS.333, GPS.333, GPS.444, GPS.444, GPS.444, GPS.555

These are hypothetical text values and will not be arranged in any specific order. I'm basically trying to see how many matches a given text value from ID1 (i.e., SLS.123) has across unique values from ID2. Another way to think about this is how many times does SLS.123 have a unique relationship with each unique text value in ID2. Please note that ideally, I'd like to avoid counting duplicates. See below:

Example Output:
SLS.123 = 4

Because:
SLS.123 & GPS.111
SLS.123 & GPS.222
SLS.123 & GPS.333
SLS.123 & GPS.444

and
SLS.123 & GPS.222 (2) not counted
SLS.123 & GPS.333 (2) not counted

I've tried all sorts of iterations of COUNTS/COUNTIFS/SUM(IF(FREQUENCE, but nothing seems to work. Any help on this would be greatly appreciated!

 

[Aladin Akyurek]

[TABLE="class: grid, width: 256"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]ID1[/TD]
[TD="width: 64, bgcolor: transparent"]ID2[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]SLS.123[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SLS.123[/TD]
[TD="bgcolor: transparent"]GPS.111[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SLS.123[/TD]
[TD="bgcolor: transparent"]GPS.222[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SLS.123[/TD]
[TD="bgcolor: transparent"]GPS.222[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SLS.234[/TD]
[TD="bgcolor: transparent"]GPS.222[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SLS.123[/TD]
[TD="bgcolor: transparent"]GPS.333[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SLS.123[/TD]
[TD="bgcolor: transparent"]GPS.333[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SLS.299[/TD]
[TD="bgcolor: transparent"]GPS.444[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SLS.123[/TD]
[TD="bgcolor: transparent"]GPS.444[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SLS.299[/TD]
[TD="bgcolor: transparent"]GPS.444[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SLS.333[/TD]
[TD="bgcolor: transparent"]GPS.555[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

In D2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$11=""),IF($A$2:$A$11=D$1,MATCH($B$2:$B$11,$B$2:$B$11,0))),ROW($B$2:$B$11)-ROW($B$2)+1),1))