Issues Sorting based on multiple columns (using SUMPRODUCT)

johnrlalor

New Member
Joined
Feb 14, 2014
Messages
38
Hi,
I have a spreadsheet for calculating golf positions after 4 rounds using the 4th, then 3rd, 2nd etc as the tie breaker. The ranking is working fine for Days 1 & 2 however when I try to rank for days 3 & 4 I can't get the functionality working.
Formulas I am using are below, any help appreciated
Day 1 & 2 rank below - Cell E2 =RANK(D2,$D$2:$D$13)+SUMPRODUCT(--($D$2:$D$13=$D2),--(C2<$C$2:$C$13))
Day 1 to 3 rank - Cell H2 =RANK(G2,$G$2:$G$13)+SUMPRODUCT(--($G$2:$G$13=$G2),--(F2<$F$2:$F$13),--(C2<$C$2:$C$13))
Overall rank - Cell K2 =RANK(J2,$J$2:$J$13)+SUMPRODUCT(--($J$2:$J$13=$J2),--(I2<$I$2:$I$13),--(F2<$F$2:$F$13),--(C2<$C$2:$C$13))
NameCourse 1Course 2Day 1 + 2 ptsDay 1 & 2 RankCourse 3Day 1 to 3 PointsDay 1 to 3 RankCourse 4Overall
Best 3 out of 4
Overall Rank
Player 131245573085633941
Player 234276132990126909
Player 3362056625811030916
Player 425285393285530908
Player 525214612267212358611
Player 636296512590127923
Player 7302353103386431941
Player 8312758426849268412
Player 9243054824781137916
Player 1031265752885633923
Player 11351853113285525923
Player 12323062227893268910
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,473
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJKL
1NameCourse 1Course 2Day 1 + 2 ptsDay 1 & 2 RankCourse 3Day 1 to 3 PointsDay 1 to 3 RankCourse 4Overall Best 3 out of 3Overall Rank
2Player 131245573085633943330241
3Player 234276132990126902629279
4Player 3362056625811030913025207
5Player 425285393285530903032288
6Player 525214612267212358635262111
7Player 636296512590127922725294
8Player 7302353103386431943133232
9Player 8312758426849268426262712
10Player 9243054824781137913724306
11Player 1031265752885633923328263
12Player 11351853113285525922532185
13Player 12323062227893268926273010
Clubs
Cell Formulas
RangeFormula
K2:K13K2=VALUE(I2&F2&C2)
L2:L13L2=RANK(J2,$J$2:$J$13)+SUMPRODUCT(--($J$2:$J$13=$J2),--(K2<$K$2:$K$13))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,473
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,488
Messages
5,706,665
Members
421,460
Latest member
MTME

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top