Adding values from adjacent cells

Hi,

New user here....wonder if anyone can help me!

I have created a spreadsheet to help calculate some results of a sports match then tabulate the players performances. Below is an example of the data:

"Player 1" 4 "Player 2" 1
"Player 2" 3 "Player 1" 2
"Player 3" 1 "Player 4" 4
"Player 4" 3 "Player 1" 2

I have used the SUMIF function to calculate the "for" figure for each player but am struggling with calcluating the "against" figure. How do I get it to add together the values of the other players?

So, if I highlight the whole range and sumif "Player 1" it totals the number of games won by each player. But how do I get it to add up the values against Player 1?

Any help appreciated!!

Assuming you have your data in A1:D4, then try:

=SUMPRODUCT((A1:A4="player 1")*(D1:D4))+SUMPRODUCT((C1:C4="Player 1")*(B1:B4))

adjust as necessary.

The formula above would yield 7 with your sample.. Is that correct?

What it is doing is looking in the first column and finding Player 1, then adding what is in the opposing teams column (column D) and then it is looking for Player 1 in column C and again summing the opposing team's scores... then adding both results together.

Great!

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

I outlined the steps in my last post.