SUMIF with INDEX MATCH Issue

clarkerots

New Member
Joined
Mar 29, 2019
Messages
10
I have the following data:

ABCDE
1Team 11Team 22
2Team 32Team 41
3Team 11Team 32
4Team 41Team 12
5Team 22Team 11
6
7Team 1-WinsTeam 1-Losses

<tbody>
</tbody>

Whenever a team wins, a 1 is entered manually into Column B or Column D. Whenever a team loses, a 2 is entered into Column B or Column D. I'm trying to create a formula that will give me Team 1 wins in B7, and Team 1 losses in D7. I'm guessing that a SUMIF would cover the task of totaling 1's or 2's, but whenever I try to couple it with a INDEX MATCH in order to sum just Team 1 information, I can only get it to read the first instance of Team 1. The final formula should read the 1's in B1, B3, and D5 to give Team 1 3 wins in B7, and read the 2's in D4 to give Team 1 1 loss in D7. Is there a way to accomplish this combination? Any help appreciated greatly!
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,168
Office Version
365
Platform
Windows
Hi, something like this maybe:

<b>Excel 2013/2016</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 /></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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;;">Team 1</td><td style="text-align: right;color: #333333;;">1</td><td style="color: #333333;;">Team 2</td><td style="text-align: right;color: #333333;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;;">Team 3</td><td style="text-align: right;color: #333333;;">2</td><td style="color: #333333;;">Team 4</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="color: #333333;;">Team 1</td><td style="text-align: right;color: #333333;;">1</td><td style="color: #333333;;">Team 3</td><td style="text-align: right;color: #333333;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="color: #333333;;">Team 4</td><td style="text-align: right;color: #333333;;">1</td><td style="color: #333333;;">Team 1</td><td style="text-align: right;color: #333333;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="color: #333333;;">Team 2</td><td style="text-align: right;color: #333333;;">2</td><td style="color: #333333;;">Team 1</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td><td style="text-align: right;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="color: #333333;;">Team 1-Wins</td><td style="text-align: right;color: #333333;background-color: #FFFF00;;">3</td><td style="color: #333333;;">Team 1-Losses</td><td style="text-align: right;color: #333333;background-color: #FFFF00;;">1</td></tr></tbody></table><p style="width:4.8em;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)">Sheet1</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)">B7</th><td style="text-align:left">=COUNTIFS(<font color="Blue">A1:A5,"Team 1",B1:B5,1</font>)+COUNTIFS(<font color="Blue">C1:C5,"Team 1",D1:D5,1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D7</th><td style="text-align:left">=COUNTIFS(<font color="Blue">A1:A5,"Team 1",B1:B5,2</font>)+COUNTIFS(<font color="Blue">C1:C5,"Team 1",D1:D5,2</font>)</td></tr></tbody></table></td></tr></table><br />
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,937
Or,

[B7] =COUNTIFS(A1:C5,"Team 1",B1:D5,1)

[D7] =COUNTIFS(A1:C5,"Team 1",B1:D5,2)

Regards
 
Last edited:

Forum statistics

Threads
1,078,364
Messages
5,339,760
Members
399,321
Latest member
ladeko

Some videos you may like

This Week's Hot Topics

Top