Hello all, this is my first post here, so hopefully, I'm doing everything right!
My goal here is to add up some values from one table in an excel spreadsheet together and have that sum show up in a separate table. To be more specific:
I have two tables: player stats, and team stats. I wish to add up all of the points scored by each player (listed in the player stats table) and have that total show up in the "points scored" table of the team stats column. For example, let's say Player 1 has 8 points and Player 2 has 6 points. Team 1's "points scored" value in the Team Stats table should add up to 14. I wish to do this for several teams.
My main problem is there are several different statistics I wish to sort my players with and want to be able to re-sort the table by each statistic without breaking the table (which is why I don't want to go with just cell referencing).
My thoughts at this stage are that, in order to make the formula robust against sorting the source data, the scores must be looked up by key and not by location. Using a dynamic array version of Excel, my suggested formula would be
@Peter Bartholomew Here's the Spreadsheet I'm working on at the moment (mind the comical names, they're aliases):
Team StatsI've gone ahead and colour coded each player to the team they belong to (i.e. Meerteo and Wingus are both red, as they're on team Meerteo Brudders, who are also filled in with red). Essentially, I want the sum of Meerteo's K value and Wingus' K value to appear under Team Meerteo Brudder's K value, if possible. Ideally, I'd like to be able to sort through each statistic on both team stats and player stats without breaking either table as well.
Your colourfully named friends present no problem. What is a problem is that there is nothing other than the wild markup colours to indicate that Meerteo and Wingus are on team Meerteo Brudders.
Players could be listed within fields of the team table or, alternatively, they could be assigned to a team on the Player table. The first option would require adapting my formula from the prior post, the other option would require the SUMIFS formula to pick out the relevant scores.