Comparisons with COUNTIFs

Question

I'm sure there's a better way!

What I'm trying to do is to count the number of times that a cell has the same alphabetic value as the key. That is, assume that row 2 has the key (correct values) for 25 questions. I want to check to see if the test taker's selection is the same as the associated question's key in the same column.

Here's what I'm generally using which forces me to have a COUNTIF for each and every column. I just want the total correct (at this time) of the answers by the test taker.

=IF(COUNTBLANK(C5:AM5)=10,0,COUNTIF(C5:C5,"="&C$2)+COUNTIF(D5:D5,"="&D$2)+COUNTIF(E5:E5,"="&E$2)+COUNTIF(F5:F5,"="&F$2)+COUNTIF(G5:G5,"="&G$2)+COUNTIF(AI5:AI5,"="&AI$2)+COUNTIF(AJ5:AJ5,"="&AJ$2)+COUNTIF(AK5:AK5,"="&AK$2)+COUNTIF(AL5:AL5,"="&AL$2))

Here's an example

Row 2 A B C A D etc.

Taker1 A C D A E total correct=2

Taker2 C B C D D total correct=3

etc.

I didn't think an XLOOKUP, VLOOKUP nor the HLOOKUP would work.

Answer 1

The number of cells in C5:AM5 that equals the corresponding cell in C2:AM2 is

=SUMPRODUCT(--(C$2:AM$2=C5:AM5))

Answer 2

tx... works!!! ;-)