SUMPRODUCT & RIGHT Functions

colesy

New Member
Joined
Jan 4, 2017
Messages
2
Hello all. I've browsed the forums for help quite a bit but this is my first post. I found posts that have got me this far but am a bit stumped now. I apologize ahead of time for the lengthy post. I thought there would be value in explaining what I am doing in detail, at the risk of people losing interest before replying.

I have a spreadsheet with multiple worksheets that contain data for a super bowl squares thing I have been running for the last 9 years. I am putting together some fun stats on different things for people to see. Here is a sample of the last few years from the main worksheet called Winners.

[TABLE="width: 400"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Quarter[/TD]
[TD]Winner[/TD]
[TD]Square[/TD]
[TD]AFC Score[/TD]
[TD]NFC Score[/TD]
[TD]Winnings[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]1[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]2[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]3[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]4[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]2[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]3[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2015[/TD]
[TD="align: right"]4[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]1[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]2[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]3[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]2016[/TD]
[TD="align: right"]4[/TD]
[TD]Names Removed[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]800[/TD]
[/TR]
</tbody>[/TABLE]

***SKIP THIS IF YOU KNOW HOW THE SUPER BOWL SQUARES WORKS***
For those that don't know how super bowl squares works there is a board with 100 squares. I happen to number the squares 1 to 100 ahead of time for ease of people picking, and some people like to pick a particular number. The actual square number is really meaningless otherwise. People buy one or more squares on the board and once they are all sold, numbers 0-9 are randomly generated along the top and side which represent the teams in the super bowl. At the end of each quarter you take the score of the game and match it up with the corresponding square on the board. For a score of 7-3 (lets assume the team along the top has 7, and the team along the side has 3) you find 7 along the top, and 3 along the side and find the square that intersects. For a double digit score you take the right most number, so 14-7, you would look for 4 and 7.
***END OF EXPLANATION***

I am working on some stats on scores that have won. Here is my worksheet for scores.

[TABLE="width: 259"]
<tbody>[TR]
[TD]Score[/TD]
[TD]Times In The Money[/TD]
[TD]Money Won[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My first issue is with the times in the money calculation. I have it working with the following formula.

=SUMPRODUCT(--(--RIGHT(Winners!E2:E37,1)=A2))+SUMPRODUCT(--(--RIGHT(Winners!F2:F37,1)=A2))

I am happy with that, except that I have to specifically define the cells its counting (E2:E37 + F2:F37). So each year when I add more data, I have to adjust the formula. If I try and use Winners!$E:$E it throws an error because it trys to add #VALUE! which gets returned for the blank fields beyond my data. I know there is an iferror function which can convert it to a value of 0, but I can't seem to figure out where to plug that function in.

My second issue if figuring out the amount of money each number has won. I'm sure the formula would be very similar to the first one, except rather than counting 1 for each true returned in the formula, I want to sum the Winnings value in the worksheet Winners.

Thank you for reading my post. If you have any suggestions, please reply.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Not entirely sure I understand your Winners sheet and how a 0/8 score in the first row awards winnings to a 6/9 square, but I assume your payouts are correct for whatever scoring you use.

Per your first question, you basically have three options for extending your range: (1) adjust your ranges in your formula after you insert a row, which sounds like you're doing now (2) have your formula reference an extra row [i.e. through row 38 instead of 37 as you have now] and insert rows above row 38 every time you want to add a new one... the formula will automatically grow to include the new rows that way or (3) search the board for "dynamic named range" and have your formula reference a named range that will automatically grow as you append new data.

For your second question, I think it should be as simple as =SUMPRODUCT(--(--RIGHT(Winners!E2:E37,1)=A2),Winners!G2:G37)+SUMPRODUCT(--(--RIGHT(Winners!F2:F37,1)=A2),Winners!G2:G37)
 
Upvote 0
Thanks Oaktree. To answer your question, the square number is simply the number of the square before the score numbers have been drawn. As I mentioned in my explanation of how the game works, I label each square 1 to 100 to simplify the picking process for people, and also, some people have lucky numbers they like to pick. There is no other significance to those numbers. I track the data because its an extra thing you can create a chart on. Even though the square number has no impact on the game, square 69 and 71 has actually won 3 times each. It's just complete luck, but some people find it interesting to know.

So the reason square 69 won on the 0/8 score is because square 69 was the intersecting square for those scores.

Your formula did the trick, but brought up new question.. More of an opinion thing though. If the score shows up in both the AFC and NFC scores should the amount won be added twice? In this formula it does. If there would be a way to not add it twice, then I can think about how I want to present the data.

Thanks again.
 
Upvote 0
Ah, that makes sense that they're just the squares' numbers and not the scores of the teams.

You could always just divide by two if you don't want to double count... =SUMPRODUCT(--(--RIGHT(Winners!E2:E37,1)=A2),Winners!G2:G37)/2+SUMPRODUCT(--(--RIGHT(Winners!F2:F37,1)=A2),Winners!G2:G37)/2

It's no doubt just luck to land on a good square, but some squares are clearly better than others. 2s and 5s are usually tough draws ^_^
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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