# Thread: SUMIF with INDEX MATCH Issue Thanks:  2 Post #5295806 (1)Post #5295890 (1) Likes: 0

1. ## SUMIF with INDEX MATCH Issue

I have the following data:

 A B C D E 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 Team 1-Losses

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!

2. ## Re: SUMIF with INDEX MATCH Issue

Hi, something like this maybe:

Excel 2013/2016
ABCD
1Team 11Team 22
2Team 32Team 41
3Team 11Team 32
4Team 41Team 12
5Team 22Team 11
6
7Team 1-Wins3Team 1-Losses1

Sheet1

Worksheet Formulas
CellFormula
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)

3. ## Re: SUMIF with INDEX MATCH Issue

Or,

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

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

Regards

4. ## Re: SUMIF with INDEX MATCH Issue

That is awesome. Thank you both!