Lottery Number Checking

Hi All,

After playing around with UK Lottery results and number selecting for the past few weeks, I've come accross an instance where I want to test if a certain pool of numbers has won a prize in the past 'x' amount of draws.

Ok, simple as I can, this is what I've got:

1> A pool of (in this case) 17 numbers picked at random. - These are placed in cells A1:A17.

2> A list of previous winning balls from the past 1162 UK Lottery draws (excluding bonus ball). Draw 1 is at B2:G2, going down to draw 1162 at B1163:G1163.

Now then, what I'm after is:

In the cell to the right of each line of draw numbers I want to know how many numbers from my pool of 17 cropped up in that particular draw. A simple figure, no text required. i.e: if all 6 winning balls were in my pool of 17, the cell would state '6', and so on.

I have seen a similar thread on this site which included a file called 'Lottery Checker'. I have played with that, but couldn't quite get it to work for me.

Any help would be fantastic. Cheers guys.

Carl

Hi madnesstiger,

Try this in H2, then drag formula down column

=COUNTIF($A$1:$A$17,B2)+COUNTIF($A$1:$A$17,C2)+COUNTIF($A$1:$A$17,D2)+COUNTIF($A$1:$A$17,E2)+COUNTIF($A$1:$A$17,F2)+COUNTIF($A$1:$A$17,G2)

Don't forget, if you win the Lottery, I'm still here!

Thanks DLL, thats much shorter