I have two rows of data in excel:

Person1's data:

country1 country2 country3 country4 score1 Personcurrenttotal
France Spain France Croatia 0-1 9

Winning data:

winningteam1 winningteam2 winningteam3 winningteam4 winningscore1
France France France Spain  0-1 

and a row of scores underneath the winning teams, that's how much would be added to the PersoncurrentTotal if country1/2/3 etc matched winningteam1/2/3 etc.

2 7 3 8 2

9 is the person's current score for the week. For each item in a person's row values, if the cell in the person's row matched the winning value, I want to add the score of that row to the person's overall score.

In this case, the 0th, 2nd and 4th rows are the same, so to 9; 2,3 and 2 are added, so then the 9 should update to 9 2 3 2 = 16.

Could someone explain the formula for me to do this in excel. I can find stuff about nested IF statements which I don't want (the row cells are independent) and I can see how to do for one cell using SUMIF, but I can't see how to just take in the rows as a whole and do the calculation that way instead of having to do it on a per-cell level?

(this excercise can only be done in excel, I could do this easily in python).

CodePudding user response：

Something like this should work. The 9 and the 16 will have to be in different cells as 'PersonOldTotal' and 'PersonNewTotal' as far as I can see. I just put them below the table: See this picture here

Formula should be:

=SUM(IF(Person1'sDataRow=WinningDataRow,RowOfScores)) PersonOldTotal

Hope this helps.