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.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team 1[/TD]
[TD]Team 2[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Team 3[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]95[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Team 4[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[TD]80[/TD]
[TD]120[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Team 1[/TD]
[TD]Team 5[/TD]
[TD][/TD]
[TD]150[/TD]
[TD]120[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Scored[/TD]
[TD]Allowed[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Team 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]D7 formula[/TD]
[TD]=SUMIFS(D1:D4,A1:A4,A7)+SUMIFS(E1:E4,B1:B4,A7)[/TD]
[TD]Totals D1+E2+E3+D4=465[/TD]
[/TR]
[TR]
[TD]E7 formula[/TD]
[TD]????[/TD]
[TD]Should total E1+D2+D3+E4=380[/TD]
[/TR]
</tbody>[/TABLE]

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.