How do I reference data from one table in a different table?

[nhanley19]

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

Hopefully, this makes sense. Thanks in advance!

[#2]

@nhanley19 

An example workbook to play with would be nice.

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

= SUM( XLOOKUP( Team["@"[PlayerA]:[PlayerB]], Individual[Player], Individual[Score] ) )

where:

Team["@"[PlayerA]:[PlayerB]] are the players that comprise the current team;

XLOOKUP returns a score for each;

SUM provides a team score.

1. I could well have got the wrong end of the stick.

2. Would need changes to work without DA

3. The quotes around the "@" symbol are only used to display here

[#3]

@Peter Bartholomew Here's the Spreadsheet I'm working on at the moment (mind the comical names, they're aliases):

Player Stats

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.

[#4]

@nhanley19 

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.

= SUMIFS( Individual[Score], Individual[Team], [@Team] )

This approach is the simpler.