Need To Total Specific Cells and Their Opposites

[clarkerots]

I'm trying to setup a list that totals 'points scored' by team and 'points allowed' by team. I can use SUMIFS to total the points scored, as I can total referencing the team name, but I'm having a hard time figuring out how to total the points allowed, as that teams name will constantly change. Data listed below.

	A	B	C	D	E	F	G
1	Team 1	Team 2		100	80
2	Team 3	Team 1		100	95
3	Team 4	Team 1		80	120
4	Team 1	Team 5		150	120
5
6				Scored	Allowed
7	Team 1

<tbody>
</tbody>

D7 formula	=SUMIFS(D1:D4,A1:A4,A7)+SUMIFS(E1:E4,B1:B4,A7)	Totals D1+E2+E3+D4=465
E7 formula	????	Should total E1+D2+D3+E4=380

<tbody>
</tbody>

The team in column A has their score in column D, and the team in column B has their score in column E. In the example, I can sum Team 1's points scored because I can reference Team 1 in my search. The issue is finding the sum of the columns opposite Team 1's score, as they will always refer to a different team name.

Any help appreciated.

 

[Marcelo Branco]

Maybe...

=SUMIF(A1:A4,A7,E1:E4)+SUMIF(B1:B4,A7,D1:D4)

M.

 

[clarkerots]

Maybe...

=SUMIF(A1:A4,A7,E1:E4)+SUMIF(B1:B4,A7,D1:D4)

M.

That works perfectly. Would you be able to briefly breakdown why this works, and which array does what?

Thank you for the response.

 

[Marcelo Branco]

This
=SUMIF(A1:A4,A7,E1:E4)
sums only the values in E1:E4 where the corresponding cells in A1:A4 are equal to A7 (Team 1)

and this
SUMIF(B1:B4,A7,D1:D4)
sums only the values in D1:D4 where the corresponding cells in B1:B4 are equal to A7 (Team 1)

M.