Sum Row Based on Conditions

[Boaesh]

I've been trying to finding the solution to my problem, but figured I'd finally reach out to the professionals.

The Table.

Row 1 would be a list of games being played. B1 would be team1 vs team2, C1 would be team3 vs team 4, D1 team5 vs team6.

Row 2 would have a list of the team that won.

Row 3 would have Bill's selections & Row 4 would be his confidence points on each team winning.
Row 5 would have John's selections & Row 6 would be his confidence points for each team winning.

I'd like to sum cell B4 to D4. The condition of the sum is based on whether each person's selected teams won the game. So Bill gets 6pts for team1 winning, 3pts for team3 winning, but no pts for his team6 selection because they lost to team5. Had team6 won, his total would be 18.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]GAME 1
[/TD]
[TD]GAME 2
[/TD]
[TD]GAME 3
[/TD]
[TD]TOTAL PTS
[/TD]
[/TR]
[TR]
[TD]this row would contain a list of winners
[/TD]
[TD]TEAM1
[/TD]
[TD]TEAM3
[/TD]
[TD]TEAM5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BILL
[/TD]
[TD]TEAM1
[/TD]
[TD]TEAM3
[/TD]
[TD]TEAM6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]JOHN
[/TD]
[TD]TEAM2
[/TD]
[TD]TEAM3
[/TD]
[TD]TEAM5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4
[/TD]
[TD]7
[/TD]
[TD]3
[/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]

 

[esseispercipi4]

There might be a more sophisticated formula, but you can just use a series of IFs:
=IF(B3=$B$2,B4,0)+IF(C3=$C$2,C4,0)+IF(D3=$D$2,D4,0)
If Bill's team in B3 matches the winner in B2, then take the points value in B4, otherwise 0. Then add the same thing in column C and the same thing in column D.

You can then copy/paste that formula to John's row, and any others. If you had additional games, you would add more IF statements to the end for whichever column you need.

 

[crazydragon84]

=sum(if(B2:D2=B3:D3,B4:D4,0)) and make it an array formula (Ctrl Shift Enter)

 

[Boaesh]

Thank you

esseispercipi4. I had something similar worked up, but I have more than just 3 columns and it was time consuming.

crazydragon84 That's exactly what I was looking for. Thank you!
​