Rank-With Tiebreaker

[natesteiner21]

I put together a NCAA Tournament Pool for a friend of mine last year and it seemed to work just fine, except, there were some ties that I should have realized would happen before I gave her the completed sheet. I will do my best to describe what I'm doing below.

Column A-Ticket # (Means Nothing Here)
Column B-Total
Column C-Rank
Column D-Rank 2
Column H-Group One (Team)
Column I-Pts_1/2 (Total Points Scored by Column H)
Column K,N,Q,T,W,Z,AC-Group 2-8 (Teams)
Column L,O,R,U,X,AA,AD-Pts_3/4_15/16 (Total Points Scored by Columns K-AC)

What I'm trying to do is whoever has the highest total in Column B is the winner, which would obviously show up in Column C. Some of the numbers in Column B are going to be the same and I have to use the tiebreaker for this, which is, total points scored in Column AD (1st TB), AA (2nd TB), X (3rd TB), U (4th TB), R (5th TB), O (6th TB), L (7th TB), I (8th TB).

The reason that I have 2 Rank Columns is that Rank is just =RANK(B2,B$2:B$3000,0)

While Rank_2 is: =IF(COUNTIF(C:C,C2)=1,C2,RANK($B2,$B$2:$B$3000)+SUMPRODUCT(($B$2:$B$3000=B2)*($AD$2:$AD$3000>$AD2))+SUMPRODUCT(($B$2:$B$3000=$B2)*($AA$2:$AA$3000>$AA2))+SUMPRODUCT(($B$2:$B$3000=B2)*($X$2:$X$3000>$X2))+SUMPRODUCT(($B$2:$B$3000=B2)*($U$2:$U$3000>$U2))+SUMPRODUCT(($B$2:$B$3000=B2)*($R$2:$R$3000>$R2))+SUMPRODUCT(($B$2:$B$3000=B2)*($O$2:$O$3000>$O2))+SUMPRODUCT(($B$2:$B$3000=B2)*($L$2:$L$3000>$L2))+SUMPRODUCT(($B$2:$B$3000=B2)*($I$2:$I$3000>$I2))+COUNTIF($C$2:C2,C2)-1)

The issue is that it isn't working correctly for every single Ticket #. Most of them it works for but not all of them.

Any help would be appreciated. Hopefully I just screwed something up typing it out.

Thanks.

 

[natesteiner21]

Ticket #	Total	Rank	Rank 2	Rank 3	Rank_M	Group One	PTS_1/2	Group Two	PTS_3/4	Group Three	PTS_5/6	Group Four	PTS_7/8	Group Five	PTS_9/10	Group Six	PTS_11/12	Group Seven	PTS_13/14	Group Eight	PTS_15/16
1323	1916	1	1			North Carolina	490	Oregon	389	Iowa State	160	South Carolina	413	Michigan State	149	Middle Tenn	153	Bucknell	80	UND	82
2481	1850	2	2			North Carolina	490	Baylor	223	Iowa State	160	South Carolina	413	Oklahoma State	91	Xavier	321	Vermont	70	UND	82
2676	1847	3	3			Gonzaga	437	Oregon	389	SMU	65	South Carolina	413	VCU	77	Xavier	321	Bucknell	80	Troy	65
576	1841	4	4			North Carolina	490	UCLA	251	Iowa State	160	South Carolina	413	Vanderbilt	66	Xavier	321	Iona	77	Jacksonville St	63
1441	1840	5	5			North Carolina	490	Florida	299	Virginia	115	South Carolina	413	Vanderbilt	66	Xavier	321	Kent State	80	Mount St. Mary's	56
1473	1828	6	6			North Carolina	490	Baylor	223	Cincinnati	142	South Carolina	413	VCU	77	Xavier	321	FGCU	80	UND	82
2804	1822	7	7			Gonzaga	437	Oregon	389	Cincinnati	142	South Carolina	413	Wichita State	129	Middle Tenn	153	Iona	77	UND	82
1025	1789	8	8			North Carolina	490	Oregon	389	Cincinnati	142	South Carolina	413	Marquette	73	Middle Tenn	153	Winthrop	64	Troy	65
1545	1788	9	9			North Carolina	490	Oregon	389	Minnesota	72	South Carolina	413	Wichita State	129	Rhode Island	161	Vermont	70	TX Southern	64
2469	1782	10	10			Gonzaga	437	UCLA	251	Minnesota	72	South Carolina	413	Wichita State	129	Xavier	321	Iona	77	UND	82
1987	1776	11	11			North Carolina	490	Purdue	226	Notre Dame	131	South Carolina	413	Vanderbilt	66	Xavier	321	NMSU	73	Mount St. Mary's	56
1168	1766	12	12			North Carolina	490	Oregon	389	Virginia	115	South Carolina	413	Marquette	73	Middle Tenn	153	Iona	77	Mount St. Mary's	56
392	1762	13	13			Gonzaga	437	UCLA	251	Maryland	65	South Carolina	413	Michigan State	149	Xavier	321	Kent State	80	SDSU	46
154	1761	14	14			North Carolina	490	Florida	299	Iowa State	160	Michigan	238	Oklahoma State	91	Xavier	321	Kent State	80	UND	82
1861	1756	15	15			North Carolina	490	Oregon	389	Maryland	65	South Carolina	413	Wichita State	129	USC	149	East Tenn St	65	Mount St. Mary's	56

<colgroup><col><col span="4"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[natesteiner21]

That is what it looks like with some formatting issues of course.