Count unique matches between two rows in Excel

Question

I've been attempting to fix this for a while, and I'm about there, but I just can't make it work.

We play a local lottery, and I have an Excel file with the results on one sheet and everyone's names and selected numbers on another.

I'm now attempting to develop a cell to track the number of numbers that each participant correctly predicted based on the numbers chosen. SUMPRODUCT(COUNTIF helped me partially, but if a number is drawn twice, it will also be counted twice in the final total.

As you can see, the number 11 should represent the total number of green cells (matches). It is now false because there are only seven green cells, not 11, in the first row.

In order to extract only the unique values using my formula, I, therefore, need some assistance. My current formula is:

=SUMPRODUCT(COUNTIF(B2:K2,Draws!$B$2:$G$18))

Answer 1

The formula should be (in Excel 365)

=SUM(--(COUNTIF(Draws!$B$2:$G$18,B2:K2)>0))

so it only counts matches in the Draws sheet once

Or

=SUMPRODUCT(--(COUNTIF(Draws!$B$2:$G$18,B2:K2)>0))

Earlier versions of Excel

The Draws sheet looks something like this: