Count matches between two columns

Question

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

The data is like so:

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

So the number I want to get to via a formula is 2, since that's how many matches there were (Bears and Chargers). Keep in mind the match has to be in the same row.

Thanks.

Answer 1

=SUMPRODUCT(--(A1:A6=B1:B6))

The double negative will convert the TRUEs and FALSEs to 1s and 0s, respectively, then sum them up.

Answer 2

I don't know of any formula that does exactly what you propose. The solution I have always used in this situation is to add a "Col C" that tests the row. Something to the effect of "=A2=B2" (in cell C2). You can then use countif ("=COUNTIF(C2:C4, TRUE)") the column to get the count you are looking for.

Answer 3

=SUMPRODUCT((A1:A6=B1:B6)*1)

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

Not any better than Dick's answer, but the "times 1" thing is easier for me to remember.