How to rank the date in Excel without duplicates

问题:

Good Morning.

I have a question about ranking date in excel, please see the image below, in the rank column,

I used formula:

=RANK($A3,$A:$A,1)

but this is not what i want.

I just want the same date share same ranking as it is shown in my Target Format column. Can anyone please tell me know what formula should I apply to achieve this goal.

Much Appreciate.

回答1:

Or you can try an array formula:

=SUMPRODUCT((A$1:A$6<A1)/COUNTIF(A$1:A$6,A$1:A$6&""))+1

Assuming date in in Column A.

Here's a good discussion on this formula: http://www.excelforum.com/excel-formulas-and-functions/672016-un-skipping-the-rank-after-a-duplicate-value-to-get-a-regular-series.html

my output:

Date        number  rank
8/15/2017   1234    1
8/15/2017   4321    1
8/15/2017   4325    1
8/17/2017   8970    2
8/17/2017   8971    2
8/20/2017   5674    3

回答2:

Try to enter this and press Shift + Ctrl + Enter altogether:

=SUM(IF(A3>$A$3:$A$8,1/COUNTIF($A$3:$A$8,$A$3:$A$8)))+1

Then drag down the formula to see the results.

Or second way (Doesn't need Shift + Ctrl + Enter):

=SUMPRODUCT((A3>A$3:A$8)/COUNTIF(A$3:A$8,A$3:A$8))+1

回答3:

Here is a quick and dirty answer that requires a helper column. For the first row, manually enter 1. For the following rows, check if the rank has changed. If the rank is the same as previous, use the previous modified rank. Otherwise, increment the previous modified rank: =IF(B3=B2,C2,C2+1).

回答4:

You can use a tiebreak column

The FinalRank is just the sum of Rank and TieBreak.

Copied from http://www.contextures.com/excel-functions-rank.html#ties

回答5:

If you sort your data by date all you need to do is write number 1 in C2 and use formula from C3 till end:

=IF(A3=A2,C2,C2+1)