# Rank Tie Break with Text, Numbers and Dates

#### buffalofan19

##### New Member
I have a spreadsheet with the following data type

Order # Date Price
AA 9/15/2014 110
AB 9/15/2014 110
AC 9/16/2014 125
AE 9/16/2014 120

What I am trying to do is rank each transaction first by the earliest date, then by the lowest price, and then, if there is still a tie by order number. The rank of the order number isn't really that important, but I just need a final tie break, and I figured alphabetically was the best way.

I know I can rank date and price with the following: =COUNTIF(\$B\$2:\$B\$6,"<"&B2)+1+SUMPRODUCT((\$B\$2:\$B\$6=B2)*(\$C\$2:\$C\$6>C2)*(A2>\$A\$2:\$A\$6)), but that would leave a tie in some transactions. For instance, Order #'s AA & AB, would both come up as "1", then AE would come up as "3", AC as "4", and AD as "5". What I need is for AB to come up as "2".

I also know I can rank the text based on alphabetical order with the following formula: =SUMPRODUCT((A2>\$A\$2:\$A\$6)+0)+1

However, I just can't seem to combine the two correctly.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Bump. Is there any way to add a third tie-breaker, or am I getting into VBA territory?

Is this just not able to be done?

Hi

You can do the criteria in sequence. For ex., in D2:

=RANK(B2,\$B\$2:\$B\$6,1)+COUNTIFS(\$B\$2:\$B\$6,B2,\$C\$2:\$C\$6,"<"&C2)+COUNTIFS(\$B\$2:\$B\$6,B2,\$C\$2:\$C\$6,C2,\$A\$2:\$A\$6,"<"&A2)

Thank you so much!

What if I wanted to rank within dates? For example, I would get 1 and 2 on 9/15 for AA and AB, and then A C and AE would be 1 & 2 on 9/16, etc.

What if I wanted to rank within dates? For example, I would get 1 and 2 on 9/15 for AA and AB, and then A C and AE would be 1 & 2 on 9/16, etc.

Just replace the Rank() with 1.

... and then A C and AE would be 1 & 2 on 9/16, etc.

If I understood correctly, on 9/16 it should be 2 & 1, not 1 & 2.

Replies
0
Views
408
Replies
9
Views
712
Replies
3
Views
70
Replies
5
Views
1K
Replies
8
Views
191

1,203,456
Messages
6,055,543
Members
444,794
Latest member
HSAL

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