Count total Non matches in two ranges

[drom]

Hi and thanks in advance!

I have two tables in excel

• Tb01
• Tb02

I have 2 Columns Named in both tables

• rcCodeTb01 'are Codes eg 5975, 0001, 75, 5987 So numbers
• rcCodeTb02 'are Codes eg 5975, 9999, 123, 12, 98 So numbers
• rcCodeTb01.Cells.count is 99% of the times <> than rcCodeTb02.Cells.Count

If I Use Conditional Cormatting within the table2 as follows

• =Y(B5<>"",CONTAR.SI(rcCodeTb01,B5)=0) 'MyTable2.DatabodyRange stars in B5
  • I get 10 cells in Green Color​

How Can I get in a Formula in B2 = 10

 

[jasonb75]

Perhaps this, Array confirmed with Shift Ctrl Enter? (untested)

=SUM(COUNTIF(rcCodeTb01,rcCodeTb02))-COUNTIF(rcCodeTb01,"<>")

 

[drom]

=SUMPRODUCT(--(rcCodeTb01<>""),--(COUNTIF(rcCodeTb02,rcCodeTb01)=0))
Does the same !