6 numbers with bonus

adulted

Active Member
Joined
Jan 22, 2004
Messages
339
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.

Thanks in advance
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.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top