tally scores over several sheets

[pevenden]

I am trying to create a summary score sheet to find a High point winner. The program has an Entries sheet where names are entered and marked as to which 'classes' they are participating in. (there are 12 classes) Each class has it's own sheet. The competitors receive a set of scores for each class which is totaled and sorted High to Low. Of a total of 30 competitors maybe 10 are in one class, they can enter several classes. 

I want to create a summary of their scores. A summary page uses this formula (=IF(Entries!$A4<>"",Entries!B4,"") to copy their names to the summary. The problem is for 'Class A' when the scores are tallied and sorted, the total does not match the competitor input line anymore. I can total a score if I chose a specific cell (='#1 (Saturday)'!T6) but it does not relate to that competitor...  Is there a way to sort that out?

[#2 · Accepted solution]

@pevenden 

To create a summary sheet that correctly tallies the scores for each competitor across multiple class sheets, even after sorting, you'll need to match the names from the Entries sheet with the names on each class sheet. This can be done using the INDEX and MATCH functions, which will allow you to retrieve the score based on the competitor's name, even if the rows have been sorted.

Here's a step-by-step guide: 

1. Structure of the Sheets:

• Entries Sheet: Contains the list of competitors and which classes they participate in.
• Class Sheets (e.g., Class A, Class B): Each contains a list of competitors with their scores.
• Summary Sheet: Where you want to summarize all scores for each competitor.

2. Using INDEX and MATCH to Retrieve Scores:

Let's assume:

• The competitors' names are in column A of each class sheet.
• The scores are in column B of each class sheet.

You want to retrieve the score for a competitor in Class A.

Formula to use in the Summary Sheet:

=IFERROR(INDEX('Class A'!$B$2:$B$30, MATCH($A4, 'Class A'!$A$2:$A$30, 0)), 0)

• 'Class A'!$B$2:$B$30: This is the range where the scores are located in the Class A sheet.
• MATCH($A4, 'Class A'!$A$2:$A$30, 0): This finds the row where the competitor's name (from the Summary Sheet, cell A4) matches the name in the Class A sheet.
• INDEX: This retrieves the score corresponding to the matched row.
• IFERROR: This ensures that if a competitor is not found in the Class A sheet, it returns 0 instead of an error.

3. Summing Scores Across Classes:

If you want to sum scores from multiple classes (e.g., Class A, Class B, Class C), you can extend the formula like this:

Formula to use in the Summary Sheet:

=IFERROR(INDEX('Class A'!$B$2:$B$30, MATCH($A4, 'Class A'!$A$2:$A$30, 0)), 0) +

 IFERROR(INDEX('Class B'!$B$2:$B$30, MATCH($A4, 'Class B'!$A$2:$A$30, 0)), 0) +

 IFERROR(INDEX('Class C'!$B$2:$B$30, MATCH($A4, 'Class C'!$A$2:$A$30, 0)), 0)

This formula will give you the total score for the competitor in cell A4 across all specified classes.

4. Automating for All Competitors:

• You can drag this formula down to apply it to all competitors in the Summary Sheet.
• Ensure that the ranges in the INDEX and MATCH functions correctly reference the entire list of names and scores in each class sheet.

5. Final Touches:

• You might want to sum the total scores and determine the high point winner by using a formula like =MAX(range_of_total_scores) to find the highest score, or =LARGE(range_of_total_scores, 1) for the highest, 2 for the second-highest, etc.

This method ensures that your summary sheet accurately reflects the scores for each competitor, even if the rows are sorted differently in each class sheet. The text, steps and formulas were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

thanks!
so, here's what I've got...
the Class Pages listed as #1(Saturday) , #1 (Sunday) , #2 (Saturday) , #3 (Sunday) etc... 12 classes, Sat and Sun.
Names are entered into each class page using this formula
=IF(Entries!$D37="*",Entries!B37,"")
The names are in Column C on each sheet, Rows 6 - 50. The total scores are in column T, rows same. For each class I reduce the list by filtering removing 'blanks' so just the entered names show up. (Each competitor competes in 3-5 classes usually, Sat and Sun)
The Summary sheet: I currently have competitors entered into the Summary using
=IF(Entries!$B4<>"",Entries!B4,"") So if there is a name entered, it gets transferred to the summary sheet.
So to get the competitor score for #1(Saturday) sheet entered I put this formula in the first cell beside the first name (where I want the score to show up, in this case E2)
=IFERROR(INDEX('#1 (Saturday)'!$T$6:$T$50, MATCH($C4, '#1 (Saturday)'!$T$6:$T$50, 0)), 0)
I pull down the cell to copy to all and I don't get any scores showing up for #1 Saturday.

what am I missing?

Thanks

[#4]

I could share the file if that helps??

[#5]

Got is sorted out... works great!!

thanks