SUMIF with INDEX MATCH Issue

[clarkerots]

I have the following data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Team 1[/TD]
[TD]1[/TD]
[TD]Team 2[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Team 3[/TD]
[TD]2[/TD]
[TD]Team 4[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Team 1[/TD]
[TD]1[/TD]
[TD]Team 3[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Team 4[/TD]
[TD]1[/TD]
[TD]Team 1[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Team 2[/TD]
[TD]2[/TD]
[TD]Team 1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Team 1-Wins[/TD]
[TD][/TD]
[TD]Team 1-Losses[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

Hi, something like this maybe:

Excel 2013/2016
	A	B	C	D
1	Team 1	1	Team 2	2
2	Team 3	2	Team 4	1
3	Team 1	1	Team 3	2
4	Team 4	1	Team 1	2
5	Team 2	2	Team 1	1
6
7	Team 1-Wins	3	Team 1-Losses	1
Sheet1

Cell Formulas
Range	Formula
B7	=COUNTIFS(A1:A5,"Team 1",B1:B5,1)+COUNTIFS(C1:C5,"Team 1",D1:D5,1)
D7	=COUNTIFS(A1:A5,"Team 1",B1:B5,2)+COUNTIFS(C1:C5,"Team 1",D1:D5,2)

 

[bosco_yip]

Or,

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

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

Regards

 

[clarkerots]

That is awesome. Thank you both!