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.
Thank you in advance...
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.
Thank you in advance...