# 6 numbers with bonus

##### Active Member
Hi All,

Been searching to no avail in the below example. Cells B6:G6 are the six drawn numbers with a bonus number in cell H6.

Picked numbers are in cells B11:G15. If you look at cells B15:G16 you'll notice there is 2 picked numbers with a bonus number. The table in cells J3:K8 should indicate there should be a \$5 payout. I need the formula modifed in cells H11:H15 to show that there are 2 + bonus that it will reflect the table.

Paul
Lotto649 Super7.xls
ABCDEFGHIJK
1wednov9
2TicketsAmounts
32 + bonus5
4310
5DrawDateonetwothreefourfivesixbonus474.7
6Wed Nov 9, 2005430394245463852397.1
75 + bonus255354.9
8616840173
9
10DrawDateonetwothreefourfivesixNumbers DrawnWinnings
11Wed Nov 9, 200525172133360#N/A
12Wed Nov 9, 20052783844462#N/A
13Wed Nov 9, 2005210224145491#N/A
14Wed Nov 9, 2005812162728440#N/A
15Wed Nov 9, 2005383542444649310
Sheet1

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### GlennUK

##### Well-known Member
Make the bonus ball worth 6 in your formula, and extent your Amounts table down so, 7 gives zero, 8 gives 5 dollars, 9 gives zero, 10 gives zero, 11 gives 255354.9, and 12 gives zero.

##### Active Member
Yikes

GlennUK

Sorry but now I'm really green. The table is a given pay amount that remains the same which determines on picks made versus drawn numbers. Maybe you can provide me an example?

Paul

#### fairwinds

##### MrExcel MVP
Hi,

Try:

H11: =SUMPRODUCT(COUNTIF(\$B\$6:\$G\$6,B11:G11))&IF(ISNUMBER(MATCH(\$H\$6,B11:G11,0))*OR(SUMPRODUCT(COUNTIF(\$B\$6:\$G\$6,B11:G11))={2,5})," + bonus","")

I11: =IF(ISNA(VLOOKUP(H11,\$J\$3:\$K\$8,2,0)),0,VLOOKUP(H11,\$J\$3:\$K\$8,2,0))

All numbers in J column must be text formatted or preceded by a single quote.
Book1
ABCDEFGHIJK
1wednov9
2TicketsAmounts
32 + bonus5
4310
5DrawDateonetwothreefourfivesixbonus474.7
6Wed Nov 9, 2005430394245463852397.1
75 + bonus255354.9
8616840173
9
10DrawDateonetwothreefourfivesixNumbers DrawnWinnings
11Wed Nov 9, 2005251721333600
12Wed Nov 9, 200527838444610
13Wed Nov 9, 20052102241454910
14Wed Nov 9, 20058121627284400
15Wed Nov 9, 20053835424446492 + bonus5
Sheet5

##### Active Member
Works like a charm

To all many thanks for your supportive help.

Paul

Replies
0
Views
253
Replies
3
Views
122
Replies
5
Views
1K
Replies
2
Views
150
Replies
3
Views
135

1,195,943
Messages
6,012,431
Members
441,700
Latest member
Warbo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back