# Need Help Finding Tournament Winners

1. ## Need Help Finding Tournament Winners

Hello and thanks so much for your time!

I'm about to be a judge in a fishing tournament for the first time, and the online system the organization uses is designed to rank winners based on simple things like length and weight. However, this tournament determines its winners...differently.

- First, Second, and Third place winners go to the people who catch at least 3 different species of fish (from five eligible species) and will be ranked on the total inches of the longest fish from each eligible category.
- The Most Valuable Angler goes to the person who catches at least 4 different species of fish (from five eligible species) and will be ranked on the total inches of the longest fish from each eligible category.
- The Top Team will go to the team who catches the greatest number of inches of eligible fish. (Edit: I think I've figured this one out already!)

As a result, it took them FOREVER to figure out who won. To avoid the same fate this year, I am trying to figure out how to automatically determine and show the winners. Luckily, I have last year's spreadsheet and tried to use pivot tables, but am not having much success.

Any and all help is much appreciated and I really am grateful to you for your time!
-JS

2. ## Re: Need Help Finding Tournament Winners

3 tables created:
(1) 1st table - summary showing max length for each Fish Family by Angler
(2) 2nd table - showing ranking for Winners
(3) 3rd table - showing ranking for Most Valuable Angler

Formula in B3=UNIQUE(Logs!D2:E365), in D3=TRANSPOSE(UNIQUE(Logs!G2:G365,FALSE)),

3. ## Re: Need Help Finding Tournament Winners

I created new sheets in the attached. The first sheet gets the 1,2, and 3rd place winners and also the Master Angler. The other sheet gets the Team winner.

A clarification - in your description, it says you add up the total inches of the LONGEST fish in each category. It looks like you were adding up ALL the inches of all fish caught in each category. Mine solution does the prior. Hopefully I interpreted it correctly.

For the 1,2,and 3 place, and the Master Angler, my first column first gets all the unique names and sorts them alphabetically:
=SORT(UNIQUE(Logs!D2:D365),,1)

Then for each of the five families, it shows the longest fish caught in each category:
=MAXIFS(Logs!\$I\$2:\$I\$365,Logs!\$D\$2:\$D\$365,\$A5#,Logs!\$G\$2:\$G\$365,B\$4)

Then I sum them up IF they caught at least 3 categories:
=IF(COUNTIFS(\$B5:\$F5,">0")>=3,SUM(\$B5:\$F5),0)

Then they are ranked:
=RANK.EQ(G5,\$G\$5:\$G\$91)

Similar to Master Angler, but I just make sure they caught at least 4 categories.

I do very similar for the Team winner. 1st I show the unique team names:
=UNIQUE(Logs!E2:E365)
Then, instead of MAXIFS, I use SUMIFS to get all the inches caught:
=SUMIFS(Logs!\$I\$2:\$I\$365,Logs!\$E\$2:\$E\$365,\$A5#,Logs!\$G\$2:\$G\$365,B\$4)
I then total them up and rank them.

4. ## Re: Need Help Finding Tournament Winners

Noted on the 5 eligible Fish Families, I have removed all the "? "and " ?" from Logs sheet.

Attached amended workbook.

5. ## Re: Need Help Finding Tournament Winners

Thank you all for your help! You helped save us HOURS!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1