Struggling with SumProduct and Max of 2 criteria

**Tol47FF** · 01-08-2018, 04:24 AM

Hello All,

Thank you for this forum it is great. I have a bit of an issue creating a Game Calculator for my casino. Capture.PNG

Basically I am trying to get a formula for the "Middle Bet" how the game works is your pay out is based on the outcome of both M16 and N16 (whichever is greater) and the wager I20. I already have the payout information in X17:X27 but am trying to get the cell to see which ever is "Greater of an outcome" in M16 and N16 and then inputting that data from X17:X27

In the photo I have the formula that in my head would work but Excel is REALLY hating me for that formula.

The other wagers "Across Bet" and "Down Bet" is solved by =SUMPRODUCT(SUMIF($Q$17:$Q$27,$N$16,$W$17:$W$27)) but its just not liking trying to figure out the max of the 2 outcomes and putting that data in.

For anyone trying to understand the casino game I am talking about it is Criss Cross Poker.

If I can help clarify anything further please let me know.

**Glenn Kennedy** · 01-08-2018, 05:03 AM

No-one will be able to help on the basis of a blurry and complicated-looking screenshot.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**Tol47FF** · 01-08-2018, 10:29 PM

I apologize about that and for the delay as the excel sheet was on my work computer. Here is the sample Work book as requested.

Outcomes are all listed in the Black Border Box and will change accordingly if you change the Patron Bets. Sheet is protected without a password so you can play someone can play with it first without worrying about changing something.

The only formula I need is the "Middle Bet where is choses the best outcome (most money) of the "Across" and "Down".

Let me know anything else I can provide to assist and thanks in advance to anyone who takes the time to try and help!!!

Tom