 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2004tattslotto numbers
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 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 >-----Original Message----- >Hi I have (and bet not the only one) about 72 games in this big tattslotto [quoted text clipped - 15 lines] > >. 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  SignatureRegards Frank Kabel Frankfurt, Germany > Hi! > [quoted text clipped - 35 lines] >> >> . Hi Frank! Duh! I misread the post. This array formula will work: =SUM(COUNTIF(A1:F1,TRANSPOSE(wn))) Biff >-----Original Message----- >Hi Biff [quoted text clipped - 44 lines] > >. Hi Biff in my test scenario even this didn't work. But could me my test case SignatureRegards Frank Kabel Frankfurt, Germany > Hi Frank! > [quoted text clipped - 59 lines] >> >> . =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 > Hi Biff > in my test scenario even this didn't work. But could me my test case [quoted text clipped - 67 lines] > >> > >> . 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 >-----Original Message----- >Hi! [quoted text clipped - 41 lines] >> >. I ended up using the countif formula including the colour as an additive. works well thanks regards to All Graham > P.S. > [quoted text clipped - 60 lines] > >> > >. I'd love to have it, but I hesitate to post a real email address to a newsgroup. Carlos > P.S. > [quoted text clipped - 60 lines] > >> > >. Hi in G1 enter the following formula: =SUMPRODUCT(--(ISNUMBER(MATCH($A1:$F1;$K$100:$K$106;0)))) SignatureRegards Frank Kabel Frankfurt, Germany > Hi I have (and bet not the only one) about 72 games in this big > tattslotto entered in cells 6 across and 72 rows [quoted text clipped - 12 lines] > Regards > Graham Wis to thank all in their reply and is now working Regards Graham > Hi > in G1 enter the following formula: [quoted text clipped - 21 lines] > > Regards > > Graham |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.