Lottery Checker counts drawn numbers more then once.

[angusn]

Hi. I'm using Excel 2010 on an XP machine.

What I have setup is a grid (C5:H325) where everyone enters lottery numbers of their choice, between 1 and 49, and in another grid (E333:K352) is where the drawn numbers are entered.

I then have off to the side in columns N325 through S325 is "=COUNTIF(winningnumbers,C325)" with the column letter changing as necessary.

What's happening is when I enter drawn numbers in the (E333:K352) grid the countif will count the number if it appears and place the total count in cell M325.

What it is not supposed to be doing and what I'm hoping for a solution for is that the countif stops counting after it has counted the number once. For it not to count duplicates. So if a number shows up 5 times from 5 draws, it's only counted once. See the number 50 & 52 below.

I've searched on this form and found many lottery forms but none that have my dilemma. Searching Google, the only thing that I could find that sounded like it might help was from the website Count Repeated Items Once. Count One Occurrence Formula but I just couldn't seem to get it to go.

Any help would be appreciated. Thank You.

 

[L. Howard]

Hi angusn,

I found this, which may do what you want. Range adjusted of course, if not correct here.

=SUM(IF(FREQUENCY(E3:J3,E3:J3)>0,1))

=SUM(IF(FREQUENCY(E3:J7,E3:J7)>0,1))

Howard

 

[L. Howard]

This seems to do the same, be careful with any text entries.

=SUMPRODUCT(1/COUNTIF(E3:J7,E3:J7))

Howard

 

[angusn]

Thanks for the suggestions Howard. I put them in but didn't see them doing what I wanted. After a couple nights sleep and a venifer infusion I believe I was thinking a little more clearly. What I did was in the cells of column M i put the following... =COUNTIF(N5:S5,"<>0")

This allows me to count the cells that contain a number, regardless of the value, and ignore any zeros.

The final product that looks like it works now.

 

[minhhieule89]

tried once and my prediction was just no where close to the real numbers