I have a spreadsheet that looks like this:
A, B, C, D, 10
A, B, X, Y, 10
A, B, C, D, 20
The first line is a template.
Latter lines are actual data. I want to count number of correct values for each line. Correct value is a value that is equal to corresponding value in the template line.
So, for second line it should be 3 and for the third line it should be 4.
I tried COUNTIF, but failed to write an expression for criterion. SUMPRODUCT also seems to be not helpful here since it count all pairs of equal values without respect to the position.
>Solution :
you can use this formula:
=SUM(--(A2:E2=$A$1:$E$1))
It checks if A2 = A1 etc, returns per each true or false, which is converted to 1 or 0 (by –) and then all the 1s and 0s are summed