# Issues Sorting based on multiple columns (using SUMPRODUCT)

#### johnrlalor

##### New Member
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))
 Name Course 1 Course 2 Day 1 + 2 pts Day 1 & 2 Rank Course 3 Day 1 to 3 Points Day 1 to 3 Rank Course 4 Overall Best 3 out of 4 Overall Rank Player 1 31 24 55 7 30 85 6 33 94 1 Player 2 34 27 61 3 29 90 1 26 90 9 Player 3 36 20 56 6 25 81 10 30 91 6 Player 4 25 28 53 9 32 85 5 30 90 8 Player 5 25 21 46 12 26 72 12 35 86 11 Player 6 36 29 65 1 25 90 1 27 92 3 Player 7 30 23 53 10 33 86 4 31 94 1 Player 8 31 27 58 4 26 84 9 26 84 12 Player 9 24 30 54 8 24 78 11 37 91 6 Player 10 31 26 57 5 28 85 6 33 92 3 Player 11 35 18 53 11 32 85 5 25 92 3 Player 12 32 30 62 2 27 89 3 26 89 10

### 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
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))

#### johnrlalor

##### New Member
That will do nicely, thanks for simplifying it for me.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
0
Views
150
Replies
5
Views
126
Replies
8
Views
294
Replies
2
Views
70
Replies
6
Views
206

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

### 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