Lookup value to the right of a cell repeatedly?

[saibur]

Hi all.

What I'd like to do is to sum the points gained in all tournaments for each player (ergo have the formula lookup the player name each time it appears, looking at the value next to it, and adding it to the sum function), while also having a dynamic sumrange, as new tournaments are guaranteed to be added in the future.

I've attached an Excel file below with three sheets: "Ranking" - Empty example with missing formula, "How I would want the ranking" - What the ranking would look like if the correct formula was implemented, and "Tournaments" - The sheet with all the tournaments.

The ideal formula would be a sum function that looks through "Tournaments", and for every time it finds the name, it adds the value to the right of that name to the sum function.

[Pete_UK]

In the Tournaments sheet, why don't you use column B for Tournament 1 (as you now have it), then column C for the second tournament, then D for the 3rd tournament etc., so that you just have one table of results? Then you could just have a simple SUM formula (say in column M) across B to L to get the current totals, even if some of those columns are blank. Then you could apply a ranking formula to the total column.

Hope this helps.

Pete

[saibur]

Hi Pete, thanks for replying.

This is unfortunately not possible as the "real" table is a lot more complicated, and the score is calculated on the basis of other parts of the individual tournament that are also a part of the table such as amount of wins and losses. I have now updated the attached Excel file to reflect that. Thanks

[Pete_UK]

You could use a formula like this in B2 of the second sheet:

=SUMIFS(Tournaments!B5:U5,Tournaments!$B$4:$U$4,"Score")

(it should automatically copy down). I've used a range out to column U, which will accommodate another table for a 3rd tournament, but you can change that parameter to suit however many columns you think you might need in the end.

Then in C2, with a column heading of Rank, you could have this formula:

=RANK(B2,B$2:B$8)+COUNTIF(B$2:B2,B2)-1

which will give you the ranks from 1 to 7. If you want to see the names and scores listed in league table order in your Ranking sheet, you could use this formula in cell A2:

=INDEX('How I would want the ranking'!A:A,MATCH(ROWS($1:1),'How I would want the ranking'!$C:$C,0))

Copy this into B2, then ensure both formulae are copied down.

Hope this helps.

Pete

[saibur]

Hi again Pete.

It appears that I've once again left something relevant out of my example file. The names in each tournament do not have a fixed position, and the amount of players in each tournament can vary, meaning that a SUMIFS function with a sum_range referring to a fixed row won't´work. I've naturally updated the file to reflect that. Very sorry for that.

[JeteMc]

This proposal employs Power Query, so if that isn't an option please ignore the remainder of the post.
1. Follow the directions in the linked video to combine the tables using the append feature: https://www.youtube.com/watch?v=b1jSi7xkmkA
2. Produce a pivot table from the combined table, both of which are modeled on the AggregateTournamentTable sheet.
3. Sort the Name column of the pivot table using more sort options > descending by > sum of score.
I believe that this would be a good option as I imagine that you will have more tournaments to add to the table in time and you could simply append again.
Let us know if you have any questions.

[saibur]

Thanks for the suggestion JeteMc. This might work, though I "solved" the issue shortly before you responded, which is why I didn't test your suggestion myself.

In case anyone is curious as to what I did, I basically created a new sheet with a player column, and several columns referring to prexisting tournament tables that counted the score of each player in the given tournament. Upon doing that, it's pretty easy to extract everything I need, it does require me to manually create a new column in the new sheet every time a new tournament appears (beyond the 30 or so ones I've already created beforehand) though.

Thanks Pete and JeteMc for you help, I'll probably take a look at the Power Query solution sometime later this week.