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

=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.