# Rank dates with multiple tiebreakers

#### rharrington10

##### New Member
Hello everyone,

I'm stuck. I've tried countless formula combinations but can't seem to get one that works 100%. I've attached a spreadsheet with sample data and highlighted my problem combination. What I'm trying to accomplish is essentially an auto rank/sort of 3 columns of dates - date 1, oldest to newest, date 2, oldest to newest and date 3, oldest to newest. Date 2 is the first tiebreaker and date 3 is the second tiebreaker.

The formula closest to the result I'm seeking, cobbled together from countless threads:

=IF(C2="","",COUNTIF(\$C\$1:\$C\$135,"<"&C2)+1+SUMPRODUCT(--(\$C\$1:\$C\$135=C2),--(\$D\$1:\$D\$135<D2))+SUMPRODUCT(--(\$C\$1:\$C\$135=C2),--(\$E\$1:\$E\$135<E2)))

I'm hoping someone can spot what I'm missing and/or doing wrong and steer me in the right direction. Any assistance is greatly appreciated.

#### Attachments

• 19.7 KB Views: 11

#### Nebu

##### Excel Ninja
Hi:

I am not sure about the logic why you are considering all three date columns for ranking. Does this work for you?

Thanks

#### Attachments

• 21.1 KB Views: 9

#### rharrington10

##### New Member
Hi:

I am not sure about the logic why you are considering all three date columns for ranking. Does this work for you?

Thanks
Thank you, Nebu. It's a seniority list, so the formula needs to incorporate all 3 dates. Date 1 is the employee seniority date. If two or more employees have the same seniority date, date 2 (hire date) (oldest to newest) is used as the first tiebreaker. If 2 or more employees have the same seniority date and hire date, date 3 (birth date) (oldest to newest) is used.

Last edited:

#### NARAYANK991

##### Excel Ninja
Hi ,

If you can make use of the following fact , then , using a helper column makes everything so simple.

All dates are at present a maximum of 5 digits ; 3 dates put together still are within the limit of 15 significant digits.

See the attached file.

Narayan

#### Attachments

• 16.2 KB Views: 14

#### rharrington10

##### New Member
Hi ,

If you can make use of the following fact , then , using a helper column makes everything so simple.

All dates are at present a maximum of 5 digits ; 3 dates put together still are within the limit of 15 significant digits.

See the attached file.

Narayan
Thank you, Narayan. At the end of the day, your solution will work, but, if possible, I would prefer to do it without a helper column.

#### Nebu

##### Excel Ninja
Hi:
Try this formula without helper column
Code:
``=RANK(C2,C\$2:C\$110,1)+SUMPRODUCT(--(C2=C\$2:C\$110),--(D2>D\$2:D\$110))+SUMPRODUCT(--(C2=C\$2:C\$110),--(D2=D\$2:D\$110),--(E2>E\$2:E\$110))``

Thanks

#### rharrington10

##### New Member
Hi:
Try this formula without helper column
Code:
``=RANK(C2,C\$2:C\$110,1)+SUMPRODUCT(--(C2=C\$2:C\$110),--(D2>D\$2:D\$110))+SUMPRODUCT(--(C2=C\$2:C\$110),--(D2=D\$2:D\$110),--(E2>E\$2:E\$110))``

Thanks
Thank you, Nebu. That seems to do the trick. I vaguely remember seeing that page in my travels. My problem is, while I can often make these formulas work, I don't always understand how they work. I appreciate your prompt response...both of them.

#### Stoy66

##### New Member
Thank you! This formula just helped me out. Just curious what does the -- do?
Hi:
Try this formula without helper column
Code:
``=RANK(C2,C\$2:C\$110,1)+SUMPRODUCT(--(C2=C\$2:C\$110),--(D2>D\$2:D\$110))+SUMPRODUCT(--(C2=C\$2:C\$110),--(D2=D\$2:D\$110),--(E2>E\$2:E\$110))``

Thanks

#### Peter Bartholomew

##### Well-Known Member
I aim to achieve boring and repetitive solutions, so I would go with
= 1+
COUNTIFS( Date1,"<"&Date1 ) +
COUNTIFS( Date1, Date1, Date2, "<"&Date2 )

COUNTIFS( Date1, Date1, Date2, Date2, Date3, "<"&Date3 )

If you still get ties then add yet another term that compares the row numbers.