tattslotto numbers

[Graham Feeley]

Hi I have (and bet not the only one) about 72 games in this big tattslotto
entered in cells 6 across and 72 rows
I would like to have excel check the winning numbers for me.
A:1 to F:1 holds the individual game
Row1 to row 72 holds the games

K100 to K 106 holds the winning numbers
a range called WN
Can anyone help me with a formula please???
I would like the column G to count the number of corresponding numbers i got
right

Hope this is the way to do this

Regards
Graham

 

[Biff]

Hi!

Try this formula entered as an array with the key combo of
CTRL,SHIFT,ENTER:

=SUM(COUNTIF(A1:F1,wn))

Also, as an added feature, you might want to
actually "see" which numbers you've matched.

Select your range of numbers.
Goto Format>Conditional Formatting.
In the dropdown select Formula IS.
Enter this formula: =OR(A1=wn)
Click the Format button.
Select a a fill color from the Pattern tab.
OK,OK

Biff

 

[Frank Kabel]

Hi
in G1 enter the following formula:
=SUMPRODUCT(--(ISNUMBER(MATCH($A1:$F1;$K$100:$K$106;0))))

 

[Frank Kabel]

Hi Biff
this won't work for this specific setup as 'wn' is not in a row but in an
column. So for example:
=SUM(COUNTIF(A1:F1,$K$100:$K$106))
will not return the expected result

 

[Biff]

P.S.

Copy the formula -

=SUM(COUNTIF(A1:F1,wn))

down to row 72 or double click the fill handle!

More P.S. -

I have a wb for checking lottery numbers. It's based on
the Pennsylvania Lottery and includes pick5, pick6 and
Powerball games. I can send you a copy if you'd like.

You can easily change it to meet your needs.

Biff

 

[Biff]

Hi Frank!

Duh!

I misread the post. This array formula will work:

=SUM(COUNTIF(A1:F1,TRANSPOSE(wn)))

Biff

 

[Graham Feeley]

Wis to thank all in their reply and is now working
Regards
Graham

 

[Graham Feeley]

I ended up using the countif formula including the colour as an additive.
works well thanks
regards to All
Graham

 

[Frank Kabel]

Hi Biff
in my test scenario even this didn't work. But could me my test case

 

[CarlosAntenna]

I'd love to have it, but I hesitate to post a real email address to a
newsgroup.

Carlos

 

[Graham Feeley]

=SUM(COUNTIF(A1:F1,WN))
this has worked fine in a column also the conditional formatting works (in
colour)
Thanks
all I have to do is include the supplimentry numbers

Regards
Graham