How can I count how many cells are the same in both rows?-CodePudding

I'm currently struggling with something that seems to me like it should be quite simple. I have the following two rows in a spreadsheet, say A and B from 1 to 5:

Strategic, Input, Restorative, Learner, Individualization

Connectedness, Strategic, Intellection, Learner, Achiever

How can I count how many words are the same in both rows? The answer in the above case should be 2.

CodePudding user response：

This array formula should work:

=SUM(IFERROR(MATCH(A:A;B:B;0)/MATCH(A:A;B:B;0);0))-1

When introducing the formula don't press enter. You have to press Control Shift Enter.

It should be shown like this, with curly brackets:

CodePudding user response：

You can do something like this in column C from row 1 to 5 and sum the result of the values.

=ZÄHLENWENNS($B$1:$B$5;A1)    //German
=COUNTIF($B$1:$B$5,A1)        //English

CodePudding user response：

You could use =SUMPRODUCT(--(COUNTIF(A1:A5,B1:B5)>0)). Sumproduct does not require ctrl shift enter also not in older Excel versions.

PS This solution is not case sensitive.