CODEWITHSUNDEEP
exceltextexcel-formulacount
pegasus123
pegasus123

Reputation: 109

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

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.

Upvotes: 0

Views: 876

Answers (3)

P.b
P.b

Reputation: 11493

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.

Upvotes: 1

Adrian Fischer
Adrian Fischer

Reputation: 387

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: enter image description here

Upvotes: 3

M1NT
M1NT

Reputation: 451

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

enter image description here

Upvotes: 1

Related Questions