tally scores over several sheets

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.