Summing A Range of Correct Matches Against Another Range-CodePudding

I have found similar posts but cant seem to get anything to work for me. Ive seen stuff like arrays but I have no clue how that works.

long story short amongst friends we started a FIFA world cup pool. We all have our answers (H, A, or D) and Im looking for a formula that will sum the correct answers for each player.

Heres a small example.

Since my knowledge is limited to basic functions like =sum(a1:a10) I'm unable to figure this one out because i'm comparing letters instead of numbers.

CodePudding user response：

Having your results (key) displayed vertically while predictions are horizontal is likely your issue. This can be fixed using the Transpose Function. Combining Transpose with with any of the below methods returns the correct value of 21 successful predictions for Player 2 (I tested on your sheet).

=Sum(ARRAYFORMULA(--(G3:BB3=transpose(KEY))))
=Sum(ARRAYFORMULA(if(G3:BB3=transpose(KEY),1,0)))
=Counta(FILTER(G3:BB3,G3:BB3=transpose(KEY)))
=SUMPRODUCT(G3:BB3=transpose(KEY))