Counting distinct values with side condition

Question

I got a table named tableName in excel like this:

ColumnA ColumnB
 1111    aaaa
 1111    bbbb
 1111    aaaa
 1111    cccc
 1111    aaaa
 1111    aaaa
 1111    aaaa
 2222    aaaa
 2222    aaaa
 2222    ffff
 2222    aaaa
 2222    aaaa
 3333    bbbb
 3333    bbbb
 4444    aaaa
 4444    bbbb

I already got this (in cell B2):

{=SUM(IF((tableName[ColumnB]=B$1)*1;1/COUNTIFS(tableName[ColumnA];tableName[ColumnA];tableName[ColumnB];B$1)))}

with aaaa in cell B1 for counting the distinct values in ColumnA where ColumnB contains aaaa. Here the result is 3 (1111 and 2222 and 4444), the marked columns are counted:

ColumnA ColumnB
 1111    aaaa      <----is counted lets call it 1
 1111    bbbb      <--------not counted because ColumnB isn't aaaa
 1111    aaaa      <----not counted because duplicate of 1
 1111    cccc      <--------not counted because ColumnB isn't aaaa
 1111    aaaa      <----not counted because duplicate of 1
 1111    aaaa      <----not counted because duplicate of 1
 1111    aaaa      <----not counted because duplicate of 1
 2222    aaaa      <----is counted lets call it 2
 2222    aaaa      <----not counted because duplicate of 2
 2222    ffff      <--------not counted because ColumnB isn't aaaa
 2222    aaaa      <----not counted because duplicate of 2
 2222    aaaa      <----not counted because duplicate of 2
 3333    bbbb      <--------not counted because ColumnB isn't aaaa
 3333    bbbb      <--------not counted because ColumnB isn't aaaa
 4444    aaaa      <----is counted lets call it 3
 4444    bbbb      <--------not counted because ColumnB isn't aaaa

Now I have got bbbb in cell A2 and want to want to count the same, but there must be an other row with the same value in ColumnA and A2(=bbbb) in ColumnB.

ColumnA ColumnB
 1111    aaaa      <----is counted because x exists
 1111    bbbb      <--------this is x
 1111    aaaa
 1111    cccc
 1111    aaaa
 1111    aaaa
 1111    aaaa
 2222    aaaa      <----not counted because no pair (2222, bbbb) exists
 2222    aaaa
 2222    ffff
 2222    aaaa
 2222    aaaa
 3333    bbbb
 3333    bbbb
 4444    aaaa      <----is counted because y exists
 4444    bbbb      <--------this is y

I don't know how to do this.

---

My complete aim is to create a contingency table like:

     aaaa bbbb cccc ffff
aaaa  3    2    1    1    
bbbb  2    3    1    0
cccc  1    1    1    0
ffff  1    0    0    1

Answer 1

Try this one:

=SUMPRODUCT((TableName[ColumnB]=B1)*  
  (0<COUNTIFS(TableName[ColumnA],TableName[ColumnA],TableName[ColumnB],A2))
/COUNTIFS(TableName[ColumnA],TableName[ColumnA],TableName[ColumnB],TableName[ColumnB]))

p.s. it's not a CSE formula, no need for Ctrl+Shift+Enter though it will work as well.