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).
>Solution :
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.