Search code examples
excelexcel-formulaunique

Count unique matches between two rows in Excel

I've been trying now for quite some time and am so close to fixing this but just can't get it work.

I got an Excel file where we do our local lottery, so sheet one is everybody their name and chosen numbers, and another sheet is the draws.

Now I'm trying to create a cell to see how many numbers each person got right according to the numbers drawn. I've partly done it with SUMPRODUCT(COUNTIF, but if a number is drawn twice, it will also be included twice in the total.

Example page of people with their chosen numbers

As you can see there is a number 11, which should be the total count of green cells (matches). Which is now incorrect, since there are for the first row just 7 green cells and not 11.

Therefore I need some help with my formula there to extract only the unique values. My formula now is:

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

  • 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))

    in earlier versions of Excel

    enter image description here

    The Draws sheet looks something like this:

    enter image description here