Finding Thunderball Matches

[guitarman43]

Hi there. I am new on here and also very new to Excel in fact a total beginner. My question is. I run a syndicate at work for the Thunderball and I wish to find out how many times each number has come out with the Thunderball i.e. 22 with Tball 8, 14 with Tball 3, etc etc. I have created the spreadsheet with all relevant data and have tried a lookup formula but all I get is (0).Your help would be greatly appreciated, many thanks.

 

[Joneye]

Here you go

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">ball drawn</td><td style="text-align: right;;"></td><td style=";">Amount of time ball drawn over duration </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">week 1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">week 2</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">week 3</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">week 4</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">week 5</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">week 6</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">week 7</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">week 8</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">week 9</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">week 10</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">week 11</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">week 12</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">week 13</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">week 14</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">week 15</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$4:$B$18,D4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$4:$B$18,E4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$4:$B$18,F4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$4:$B$18,G4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$4:$B$18,H4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$4:$B$18,I4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$4:$B$18,J4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K5</th><td style="text-align:left">=COUNTIF(<font color="Blue">$B$4:$B$18,K4</font>)</td></tr></tbody></table></td></tr></table><br />

 

[guitarman43]

Many thanks for that Joneye but unfortunately it does not work. Is it because I have Excel 2007?. My layout is.
NUMBERS TBALL RESULT COLUMNS
B C D E F G J K L M N O P Q R S T U V W
1 2 3 4 5 5 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
5 6 7 8 9 12

MANY THANKS

 

[Joneye]

PLease explain how you are failing?

Are you copying and pasting the formula?

 

[guitarman43]

I have pasted the formula but I had to change the cells from D5 E5 etc to J2 K2 etc my layout is like this
<table border="0" cellpadding="0" cellspacing="0" width="920"><col style="width:30pt" width="40"> <col style="width:30pt" span="5" width="40"> <col style="width:30pt" width="40"> <col style="width:30pt" span="2" width="40"> <col style="width:30pt" span="14" width="40"> <tbody><tr style="height:15.75pt" height="21"> <td style="height:15.75pt;width:30pt" height="21" width="40">
</td> <td class="xl63" style="width:30pt" width="40">1</td> <td class="xl63" style="width:30pt" width="40">2</td> <td class="xl63" style="width:30pt" width="40">3</td> <td class="xl63" style="width:30pt" width="40">4</td> <td class="xl63" style="width:30pt" width="40">5</td> <td class="xl64" style="width:30pt" width="40">TBALL</td> <td style="width:30pt" width="40">
</td> <td style="width:30pt" width="40">
</td> <td class="xl65" style="width:30pt" width="40">1</td> <td class="xl65" style="width:30pt" width="40">2</td> <td class="xl65" style="width:30pt" width="40">3</td> <td class="xl65" style="width:30pt" width="40">4</td> <td class="xl65" style="width:30pt" width="40">5</td> <td class="xl65" style="width:30pt" width="40">6</td> <td class="xl65" style="width:30pt" width="40">7</td> <td class="xl65" style="width:30pt" width="40">8</td> <td class="xl65" style="width:30pt" width="40">9</td> <td class="xl65" style="width:30pt" width="40">10</td> <td class="xl65" style="width:30pt" width="40">11</td> <td class="xl65" style="width:30pt" width="40">12</td> <td class="xl65" style="width:30pt" width="40">13</td> <td class="xl65" style="width:30pt" width="40">14</td> </tr> <tr style="height:15.75pt" height="21"> <td style="height:15.75pt" align="right" height="21">1</td> <td class="xl63">8</td> <td class="xl63">26</td> <td class="xl63">28</td> <td class="xl63">31</td> <td class="xl63">32</td> <td class="xl64">6</td> <td>
</td> <td>
</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> </tr> <tr style="height:15.75pt" height="21"> <td style="height:15.75pt" align="right" height="21">2</td> <td class="xl63">3</td> <td class="xl63">4</td> <td class="xl63">8</td> <td class="xl63">20</td> <td class="xl63">33</td> <td class="xl64">7</td> <td>
</td> <td>
</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> </tr> <tr style="height:15.75pt" height="21"> <td style="height:15.75pt" align="right" height="21">3</td> <td class="xl63">7</td> <td class="xl63">16</td> <td class="xl63">19</td> <td class="xl63">23</td> <td class="xl63">26</td> <td class="xl64">3</td> <td>
</td> <td>
</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> </tr> <tr style="height:15.75pt" height="21"> <td style="height:15.75pt" align="right" height="21">4</td> <td class="xl63">1</td> <td class="xl63">16</td> <td class="xl63">20</td> <td class="xl63">27</td> <td class="xl63">30</td> <td class="xl64">8</td> <td>
</td> <td>
</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> </tr> </tbody></table>so I have to find how many times (say 18 has come out with Tball 6 and 37 with Tball 3 I hope I have got it right but as I said I am a novice at this many thanks for your time.

 

[guitarman43]

Hi there Joneye. I tried to reply yesterday but the forum was down all day. I did manage a reply on the evening before but I don;t know whether you picked it up. anyway here is what I said.
I have pasted the formula but I had to change the cells from D5 E5 etc to J2 K2 etc my layout is like this
A B C D E F G H I J K L M N O P Q R S T U V W
<table border="0" cellpadding="0" cellspacing="0" width="920"><colgroup><col style="width:30pt" width="40"> <col style="width:30pt" span="5" width="40"> <col style="width:30pt" width="40"> <col style="width:30pt" span="2" width="40"> <col style="width:30pt" span="14" width="40"> </colgroup><tbody><tr style="height:15.75pt" height="21"> <td style="height:15.75pt;width:30pt" height="21" width="40">
</td> <td class="xl63" style="width:30pt" width="40">1</td> <td class="xl63" style="width:30pt" width="40">2</td> <td class="xl63" style="width:30pt" width="40">3</td> <td class="xl63" style="width:30pt" width="40">4</td> <td class="xl63" style="width:30pt" width="40">5</td> <td class="xl64" style="width:30pt" width="40">TBALL</td> <td style="width:30pt" width="40">
</td> <td style="width:30pt" width="40">
</td> <td class="xl65" style="width:30pt" width="40">1</td> <td class="xl65" style="width:30pt" width="40">2</td> <td class="xl65" style="width:30pt" width="40">3</td> <td class="xl65" style="width:30pt" width="40">4</td> <td class="xl65" style="width:30pt" width="40">5</td> <td class="xl65" style="width:30pt" width="40">6</td> <td class="xl65" style="width:30pt" width="40">7</td> <td class="xl65" style="width:30pt" width="40">8</td> <td class="xl65" style="width:30pt" width="40">9</td> <td class="xl65" style="width:30pt" width="40">10</td> <td class="xl65" style="width:30pt" width="40">11</td> <td class="xl65" style="width:30pt" width="40">12</td> <td class="xl65" style="width:30pt" width="40">13</td> <td class="xl65" style="width:30pt" width="40">14</td> </tr> <tr style="height:15.75pt" height="21"> <td style="height:15.75pt" align="right" height="21">1</td> <td class="xl63">8</td> <td class="xl63">26</td> <td class="xl63">28</td> <td class="xl63">31</td> <td class="xl63">32</td> <td class="xl64">6</td> <td>
</td> <td>
</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> </tr> <tr style="height:15.75pt" height="21"> <td style="height:15.75pt" align="right" height="21">2</td> <td class="xl63">3</td> <td class="xl63">4</td> <td class="xl63">8</td> <td class="xl63">20</td> <td class="xl63">33</td> <td class="xl64">7</td> <td>
</td> <td>
</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> </tr> <tr style="height:15.75pt" height="21"> <td style="height:15.75pt" align="right" height="21">3</td> <td class="xl63">7</td> <td class="xl63">16</td> <td class="xl63">19</td> <td class="xl63">23</td> <td class="xl63">26</td> <td class="xl64">3</td> <td>
</td> <td>
</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> </tr> <tr style="height:15.75pt" height="21"> <td style="height:15.75pt" align="right" height="21">4</td> <td class="xl63">1</td> <td class="xl63">16</td> <td class="xl63">20</td> <td class="xl63">27</td> <td class="xl63">30</td> <td class="xl64">8</td> <td>
</td> <td>
</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> <td class="xl66">0</td> </tr> </tbody></table>so I have to find how many times (say 18 has come out with Tball 6 and 37 with Tball 3 I hope I have got it right but as I said I am a novice at this many thanks for your time

 

[ukmikeb]

Hi

On the same sheet as your Thunderball results reserve 39 columns headed 1 through 39 and 14 Thunderball Columns headed 1 to 14.

Assuming the first row if your data is row 2 in the first cell in column headed 1 enter -

=--($A2:$E2=I$1)

Enter this with Control - Shift - Enter ( it is an array formula).
(copying your data to a spreadsheet I have assumed column I)
copy across to the column headed 39 and drag down for all the draws.

Do similarly for the Thunderball with -

=--($F2=Y$1)

changing Y$1 to reference the column where the Thunderball numbers start.
copy across and down as above.

On a separate sheet created a 14 by 39 grid labelled in row 1 and Column A.

In the first cell B2 enter -

=SUMPRODUCT(--(INDEX(Sheet1!$I$2:$V$5,,$A2)=1),--(INDEX(Sheet1!$Y$2:$AF$5,,B$1)=1))

with Control - Shift - Enter
Alter Sheet1!$I$2:$V$5 to reflect the range where the 0,1s for the numbers are stored and alter Sheet1!$Y$2:$AF$5 to reflect the range where the 0,1s for the Thunderball numbers are stored.
copy across and down.

In this grid you should find the information required at the relevant intersect.

hth

 

[guitarman43]

Hi there UKMIKEB. Thanks very much for the info you gave me.I have done as you said on the same sheet as the Tball results I have reserved 39 columns going from H-AT. And again on the same sheet I have reserved 14 columns going from AV-BI. I then entered the formula =--($A2:$E2=I$1)
and then pressed Ctrl-shift-enter and the return was (FALSE). So what have I done wrong. as you may have realised I am very much a novice so many thanks for your time.
A B C D E F H I J K L M N O P Q etc AV AW AX AY AZ etc
Tball Results Tball Reserved columns Reserved columns
Number 1-39 1-14

 

[ukmikeb]

Hi

Here is the first part of your grid with the formula in H2 -

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5	TBALL		1	2	3	4	5
2	8	26	28	31	32	6		0	0	0	0	0
3	3	4	8	20	33	7		0	0	1	1	0
4	7	16	19	23	26	3		0	0	0	0	0
5	1	16	20	27	30	8		1	0	0	0	0
Sheet14

Excel 2007

Cell Formulas
Range	Formula
BG2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BG$1)=1))
BG3	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE3)=1),--(INDEX($AV$2:$BC$5,,BG$1)=1))
BG4	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE4)=1),--(INDEX($AV$2:$BC$5,,BG$1)=1))
BG5	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE5)=1),--(INDEX($AV$2:$BC$5,,BG$1)=1))
BH2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BH$1)=1))
BH3	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE3)=1),--(INDEX($AV$2:$BC$5,,BH$1)=1))
BH4	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE4)=1),--(INDEX($AV$2:$BC$5,,BH$1)=1))
BH5	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE5)=1),--(INDEX($AV$2:$BC$5,,BH$1)=1))
BI2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BI$1)=1))
BI3	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE3)=1),--(INDEX($AV$2:$BC$5,,BI$1)=1))
BI4	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE4)=1),--(INDEX($AV$2:$BC$5,,BI$1)=1))
BI5	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE5)=1),--(INDEX($AV$2:$BC$5,,BI$1)=1))
BJ2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BJ$1)=1))
BJ3	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE3)=1),--(INDEX($AV$2:$BC$5,,BJ$1)=1))
BJ4	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE4)=1),--(INDEX($AV$2:$BC$5,,BJ$1)=1))
BJ5	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE5)=1),--(INDEX($AV$2:$BC$5,,BJ$1)=1))
BK2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BK$1)=1))
BK3	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE3)=1),--(INDEX($AV$2:$BC$5,,BK$1)=1))
BK4	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE4)=1),--(INDEX($AV$2:$BC$5,,BK$1)=1))
BK5	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE5)=1),--(INDEX($AV$2:$BC$5,,BK$1)=1))
BL2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BL$1)=1))
BL3	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE3)=1),--(INDEX($AV$2:$BC$5,,BL$1)=1))
BL4	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE4)=1),--(INDEX($AV$2:$BC$5,,BL$1)=1))
BL5	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE5)=1),--(INDEX($AV$2:$BC$5,,BL$1)=1))
BM2	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE2)=1),--(INDEX($AV$2:$BC$5,,BM$1)=1))
BM3	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE3)=1),--(INDEX($AV$2:$BC$5,,BM$1)=1))
BM4	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE4)=1),--(INDEX($AV$2:$BC$5,,BM$1)=1))
BM5	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE5)=1),--(INDEX($AV$2:$BC$5,,BM$1)=1))
BF3	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE3)=1),--(INDEX($AV$2:$BC$5,,BF$1)=1))
BF4	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE4)=1),--(INDEX($AV$2:$BC$5,,BF$1)=1))
BF5	=SUMPRODUCT(--(INDEX($H$2:$U$5,,$BE5)=1),--(INDEX($AV$2:$BC$5,,BF$1)=1))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
oops, only selected one cell formula there!

However, I have referred back to my own lottery project and the formula in H2 is more robust.

Hoping the above is clearer to you.

 

[Joneye]

Here again, my workings will show how many times the ball appears, note the "$" sign this locks the range of were the forumla works from and to

So If you expand it from F6 to F50 be sure to put it the $ sign. The workings count how many times the thunder ball appears in the column F.



<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">D</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">E</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">F</td><td style="font-weight: bold;text-align: center;;">G</td><td style="font-weight: bold;text-align: center;;">H</td><td style="font-weight: bold;text-align: center;;">I</td><td style="font-weight: bold;text-align: center;;">J</td><td style="font-weight: bold;text-align: center;;">K</td><td style="font-weight: bold;text-align: center;;">L</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TBALL</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">28</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">31</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">32</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">33</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">16</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">19</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">23</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">16</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">27</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">30</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$F$3:$F$6,H$2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$F$3:$F$6,I$2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$F$3:$F$6,J$2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$F$3:$F$6,K$2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$F$3:$F$6,L$2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$F$3:$F$6,M$2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$F$3:$F$6,N$2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O3</th><td style="text-align:left">=COUNTIF(<font color="Blue">$F$3:$F$6,O$2</font>)</td></tr></tbody></table></td></tr></table><br />