What to use to know how many items from 1 colums are in common with other column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all.
Looking for a bit of help. I have 2 columns with 20 items in each columns. I want a formula to know how many items in the first column are a match with the second column (match can be anywhere in the column).

Ex:
Column1 Column
AAA BB
CCC AA
DDD EE
FFF CC

So, my answer would be 2 in this example

Thank You in advance for any help!
 
Hello "Codex"

One way assuming no duplicates in C2:C21:

Duplicates allowed in D2:D21

=SUMPRODUCT(NOT(ISERROR(MATCH(C2:C21,D2:D21,0)))+0)


If you never have duplicates in D2:D21, this formula
is an alternative:

=SUM((C2:C21=TRANSPOSE(D2:D21))+0)

This formula must be entered with <Shift><Ctrl><Enter>,
also if edited later.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Codex said:
Hello all.
Looking for a bit of help. I have 2 columns with 20 items in each
Click to expand...
columns. I want a formula to know how many items in the first column are a
match with the second column (match can be anywhere in the column).
 
Back
Top