# Rank Based On Earliest Date and Score 100

##### Well-known Member
hi all..

how to make rank formula based on earliest date and score.
the score can given rank is always 100.
date format in dd/mm/yyyy
here this table:
 name score date rank jhon 90 10/10/2020 mike 100 31/01/2020 rank-1 dylan 80 26/07/2020 ish 100 12/12/2020 rank-2 stec 100 17/31/2021 rank-3

any help, great appreciated..
.sst

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

##### Well-known Member
sorry i missing in last row should be :
 name score date rank jhon 90 10/10/2020 mike 100 31/01/2020 rank-1 dylan 80 26/07/2020 ish 100 12/12/2020 rank-2 stec 100 17/03/2021 rank-3 correction

across posting

#### Peter_SSs

##### MrExcel MVP, Moderator
Like this?

20 12 08.xlsm
ABCD
1namescoredaterank
2jhon9010/10/2020
3mike10031/01/20201
4dylan8026/07/2020
5ish10012/12/20202
6stec10017/03/20213
Rank
Cell Formulas
RangeFormula
D2:D6D2=IF(B2=100,SUMPRODUCT((B\$2:B\$6=100)*(C2>C\$2:C\$6))+1,"")

##### Well-known Member
hi Peter..
thank you so much!! Working not fully.
if date same the rank same too..should be the rank not same
like this :
 name score date jhon 90 10/10/2020 mike 100 31/01/2020 1 dylan 80 26/07/2020 ish 100 12/12/2020 2 stec 100 12/12/2020 2 should be 3

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
Try
Excel Formula:
``=IF(B2=100,SUMPRODUCT((B\$2:B\$6=100)*(C2>C\$2:C\$6))+COUNTIFS(B\$2:B2,100,C\$2:C2,C2),"")``

Replies
2
Views
55
Replies
4
Views
71
Replies
2
Views
111
Replies
3
Views
43
Replies
6
Views
216

1,129,713
Messages
5,637,927
Members
416,993
Latest member
ant8989

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

### Which adblocker are you using?

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

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