Summing Multiple Columns and Rows

AllAboutAlly

New Member
Joined
Mar 9, 2019
Messages
1
I've generated a worksheet for pari-mutual betting and I've hit a road-block. I added results to range L1:N4. I need a formula in cells M7 and M8 that will search for winning bets and calculate the total payout due to the bettor. This worksheet has many more rows and several more columns. I made the odds 1/1 to simplify.
Can anyone help?


Excel 2016 (Windows) 32 bit
ABCDEFGHIJKLMN
1LAST NAME/FIRST NAMETEAM NUMBERWAGER TYPEAMOUNTTEAM NUMBERWAGER TYPEAMOUNTTEAM NUMBERWAGER TYPEAMOUNTRESULTSTEAM MEMBERODDS
2Ambler, Rosendo23FLIGHT WIN519OVERALL SHOW2534FLIGHT PLACE10OVERALL WIN701/1
3Burgo, Mose66FLIGHT PLACE1041FLIGHT WIN3010FLIGHT SHOW15OVERALL PLACE141/1
4Higham, Herta83FLIGHT SHOW1564FLIGHT PLACE3589OVERALL WIN20OVERALL SHOW381/1
5Elvin, Domingo70OVERALL WIN2024FLIGHT SHOW4014OVERALL PLACE25
6Shiffer, Alene90OVERALL PLACE2520OVERALL WIN4517OVERALL SHOW30BETTORPAYOUT
7Tome, Norberto21OVERALL SHOW3014OVERALL PLACE509FLIGHT WIN35Elvin, Domingo45
8Steveson, Mignon27FLIGHT WIN3538OVERALL SHOW590FLIGHT PLACE40Vrba, Benito40
9Hoehne, Marti66FLIGHT PLACE4062FLIGHT WIN1050FLIGHT SHOW45
10Bingaman, Elba17FLIGHT SHOW454FLIGHT PLACE1571OVERALL WIN50
11Wollard, Kathleen30OVERALL WIN5031FLIGHT SHOW2059OVERALL PLACE5
12Vrba, Benito14OVERALL PLACE570OVERALL WIN2538OVERALL SHOW10
13Taitt, Melissa59OVERALL SHOW1038OVERALL SHOW2546FLIGHT WIN15
14Grasty, Ismael13FLIGHT WIN1587FLIGHT WIN3085FLIGHT PLACE20
15Valenzuela, Tesha5FLIGHT PLACE2035FLIGHT PLACE3586FLIGHT SHOW25
16Cowan, Lennie83FLIGHT SHOW2591FLIGHT SHOW4054OVERALL WIN30
17Sanchez, Janet3OVERALL WIN3044OVERALL WIN4514OVERALL PLACE35
18Griffin, Brian91OVERALL PLACE3595OVERALL PLACE5079OVERALL SHOW40
19Bailey, Keith47OVERALL SHOW4084OVERALL SHOW549FLIGHT WIN45
20Mitchell, Jeremy39FLIGHT WIN4523FLIGHT WIN108FLIGHT PLACE50
21
22

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Would summing row by row work for you?

K2 formula with your criterion hard-coded.
L2 formula uses cell references for criterion.

Either formula copied down:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><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: rgb(22,17,32);text-align: center;">1</td><td style=";">LAST NAME/FIRST NAME</td><td style=";">TEAM NUMBER</td><td style=";">WAGER TYPE</td><td style=";">AMOUNT</td><td style=";">TEAM NUMBER</td><td style=";">WAGER TYPE</td><td style=";">AMOUNT</td><td style=";">TEAM NUMBER</td><td style=";">WAGER TYPE</td><td style=";">AMOUNT</td><td style=";">PAYOUT</td><td style=";">PAYOUT</td><td style=";">RESULTS</td><td style=";">TEAM MEMBER</td><td style=";">ODDS</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Ambler, Rosendo</td><td style="text-align: right;;">23</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">5</td><td style="text-align: right;;">19</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">25</td><td style="text-align: right;;">34</td><td style=";">FLIGHT PLACE</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";">OVERALL WIN</td><td style="text-align: right;;">70</td><td style=";">1/1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Burgo, Mose</td><td style="text-align: right;;">66</td><td style=";">FLIGHT PLACE</td><td style="text-align: right;;">10</td><td style="text-align: right;;">41</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">30</td><td style="text-align: right;;">10</td><td style=";">FLIGHT SHOW</td><td style="text-align: right;;">15</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";">OVERALL PLACE</td><td style="text-align: right;;">14</td><td style=";">1/1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Higham, Herta</td><td style="text-align: right;;">83</td><td style=";">FLIGHT SHOW</td><td style="text-align: right;;">15</td><td style="text-align: right;;">64</td><td style=";">FLIGHT PLACE</td><td style="text-align: right;;">35</td><td style="text-align: right;;">89</td><td style=";">OVERALL WIN</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">38</td><td style=";">1/1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Elvin, Domingo</td><td style="text-align: right;;">70</td><td style=";">OVERALL WIN</td><td style="text-align: right;;">20</td><td style="text-align: right;;">24</td><td style=";">FLIGHT SHOW</td><td style="text-align: right;;">40</td><td style="text-align: right;;">14</td><td style=";">OVERALL PLACE</td><td style="text-align: right;;">25</td><td style="text-align: right;;">45</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Shiffer, Alene</td><td style="text-align: right;;">90</td><td style=";">OVERALL PLACE</td><td style="text-align: right;;">25</td><td style="text-align: right;;">20</td><td style=";">OVERALL WIN</td><td style="text-align: right;;">45</td><td style="text-align: right;;">17</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">30</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";">BETTOR</td><td style=";">PAYOUT</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Tome, Norberto</td><td style="text-align: right;;">21</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">30</td><td style="text-align: right;;">14</td><td style=";">OVERALL PLACE</td><td style="text-align: right;;">50</td><td style="text-align: right;;">9</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">35</td><td style="text-align: right;;">50</td><td style="text-align: right;;">50</td><td style=";">Elvin, Domingo</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Steveson, Mignon</td><td style="text-align: right;;">27</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">35</td><td style="text-align: right;;">38</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">5</td><td style="text-align: right;;">90</td><td style=";">FLIGHT PLACE</td><td style="text-align: right;;">40</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style=";">Vrba, Benito</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Hoehne, Marti</td><td style="text-align: right;;">66</td><td style=";">FLIGHT PLACE</td><td style="text-align: right;;">40</td><td style="text-align: right;;">62</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">10</td><td style="text-align: right;;">50</td><td style=";">FLIGHT SHOW</td><td style="text-align: right;;">45</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Bingaman, Elba</td><td style="text-align: right;;">17</td><td style=";">FLIGHT SHOW</td><td style="text-align: right;;">45</td><td style="text-align: right;;">4</td><td style=";">FLIGHT PLACE</td><td style="text-align: right;;">15</td><td style="text-align: right;;">71</td><td style=";">OVERALL WIN</td><td style="text-align: right;;">50</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Wollard, Kathleen</td><td style="text-align: right;;">30</td><td style=";">OVERALL WIN</td><td style="text-align: right;;">50</td><td style="text-align: right;;">31</td><td style=";">FLIGHT SHOW</td><td style="text-align: right;;">20</td><td style="text-align: right;;">59</td><td style=";">OVERALL PLACE</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Vrba, Benito</td><td style="text-align: right;;">14</td><td style=";">OVERALL PLACE</td><td style="text-align: right;;">5</td><td style="text-align: right;;">70</td><td style=";">OVERALL WIN</td><td style="text-align: right;;">25</td><td style="text-align: right;;">38</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">10</td><td style="text-align: right;;">40</td><td style="text-align: right;;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Taitt, Melissa</td><td style="text-align: right;;">59</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">10</td><td style="text-align: right;;">38</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">25</td><td style="text-align: right;;">46</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">15</td><td style="text-align: right;;">25</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Grasty, Ismael</td><td style="text-align: right;;">13</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">15</td><td style="text-align: right;;">87</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">30</td><td style="text-align: right;;">85</td><td style=";">FLIGHT PLACE</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Valenzuela, Tesha</td><td style="text-align: right;;">5</td><td style=";">FLIGHT PLACE</td><td style="text-align: right;;">20</td><td style="text-align: right;;">35</td><td style=";">FLIGHT PLACE</td><td style="text-align: right;;">35</td><td style="text-align: right;;">86</td><td style=";">FLIGHT SHOW</td><td style="text-align: right;;">25</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Cowan, Lennie</td><td style="text-align: right;;">83</td><td style=";">FLIGHT SHOW</td><td style="text-align: right;;">25</td><td style="text-align: right;;">91</td><td style=";">FLIGHT SHOW</td><td style="text-align: right;;">40</td><td style="text-align: right;;">54</td><td style=";">OVERALL WIN</td><td style="text-align: right;;">30</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Sanchez, Janet</td><td style="text-align: right;;">3</td><td style=";">OVERALL WIN</td><td style="text-align: right;;">30</td><td style="text-align: right;;">44</td><td style=";">OVERALL WIN</td><td style="text-align: right;;">45</td><td style="text-align: right;;">14</td><td style=";">OVERALL PLACE</td><td style="text-align: right;;">35</td><td style="text-align: right;;">35</td><td style="text-align: right;;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Griffin, Brian</td><td style="text-align: right;;">91</td><td style=";">OVERALL PLACE</td><td style="text-align: right;;">35</td><td style="text-align: right;;">95</td><td style=";">OVERALL PLACE</td><td style="text-align: right;;">50</td><td style="text-align: right;;">79</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">40</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Bailey, Keith</td><td style="text-align: right;;">47</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">40</td><td style="text-align: right;;">84</td><td style=";">OVERALL SHOW</td><td style="text-align: right;;">5</td><td style="text-align: right;;">49</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">45</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Mitchell, Jeremy</td><td style="text-align: right;;">39</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">45</td><td style="text-align: right;;">23</td><td style=";">FLIGHT WIN</td><td style="text-align: right;;">10</td><td style="text-align: right;;">8</td><td style=";">FLIGHT PLACE</td><td style="text-align: right;;">50</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</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:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet634</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">=SUM(<font color="Blue">SUMIFS(<font color="Red">D2:J2,C2:I2,{"OVERALL WIN","OVERALL PLACE","OVERALL SHOW"},B2:H2,{70,14,38}</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L2</th><td style="text-align:left">=SUMIFS(<font color="Blue">D2:J2,B2:H2,N$2,C2:I2,M$2</font>)+SUMIFS(<font color="Blue">D2:J2,B2:H2,N$4,C2:I2,M$4</font>)+SUMIFS(<font color="Blue">D2:J2,B2:H2,N$3,C2:I2,M$3</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,109,445
Messages
5,528,800
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top