Based on your screenshots, I would have 2 tables:
A Users table: this is your 2nd screenshot with the list of names of the users of the app. It will have names, email addresses, perhaps an image, a role, and then a series of columns with points. You could have a separate Points table, but at this point I don’t really see the point (haha, pun intended), I would keep points in the Users table.
A Games table: exactly as you showed it in your 1st screenshot, with a date, player 1, player 1 score, etc.
Now for the formula in F2. It’s a sum of 2 SUMIF, so you’ll be storing the value of each SUMIF in a separate column. F2 = (E2 + E3 + E10 + E11) + (C8 + C9)
Column 1: (E2 + E3 + E10 + E11)
Column 2: (C8 + C9)
In the Users table:
- Create a
relation column, match multiple, where Name (in the Users table) is Player 1 in the Games table.
- Create a
lookup column, data source is the relation column just above, and look up the values Player 2 Score.
- Create a
rollup column and do a sum on the lookup column just above.
In a nutshell, in the Users table, you are looking at the name in the row, taking that value and matching it with values in a column of another table, then where there is a match you are gathering the values, and finally you are summing them.
If you manage to set that up correctly, repeat for the second SUMIF.
Finally, create a math column to sum the two sums.
Note 1: Somewhere in there perhaps you could use a Query column instead of the relation, but if I were you I would stick to the relation.
Note 2: It is a good practice to create relations based on unique values that will never change such as a row ID. In your example, imagine you had a new player join the group and his name was “John”. It could start getting messy and confusing. Or if you decided to change the names to first name + family name, this would destroy your relation. But a relation based on row IDs (which are unique and immutable) will not break. Once you get the hang of relations, you can redo them based on row IDs. At first I would practice with names though, it’ll make it easier for you to visualize what you’re doing.
