All geek questions in one place

The number of matches between two columns

In Excel, I have two columns. One is a prediction, one is a result. I want to calculate how many times the prediction matches the result (i.e., Correct prediction).

The data is the same:

Col A Col B Bears Bears Chiefs Raiders Chargers Chargers Colts Texans Lions Packers

Thus, the number that I want to get by the formula is 2 , since the number of matches was (Bears and Chargers). Keep in mind that the match must be on the same line .

Thanks.

+6
source share
3 answers
 =SUMPRODUCT(--(A1:A6=B1:B6))

A double negative converts TRUE and FALSE to 1s and 0s, respectively, and then sums them.

+15
source 
 =SUMPRODUCT((A1:A6=B1:B6)*1)

The result of the array equality expression will be {TRUE,FALSE,TRUE,FALSE,FALSE,FALSE} so you have an intermediate expression =SUMPRODUCT(({TRUE,FALSE,TRUE,FALSE,FALSE,FALSE})*1) since TRUE*1=1 , it gets you =SUMPRODUCT({1,0,1,0,0,0}) , which gets you 2 .

Not better than Dick, but it’s easier for me to remember “time 1”.

+3
source

I do not know any formula that does exactly what you offer. The solution that I have always used in this situation is to add "Col C", which checks the string. Something under the influence of "= A2 = B2" (in cell C2). Then you can use the countif ("= COUNTIF (C2: C4, TRUE)") column to get the score you are looking for.

0
source

Source: https://habr.com/ru/post/898092/

More articles:


All Articles