Jan 15, 2010

I've got 3 columns of info.. Name, Org, #Sales, $$..

I need to rank the list by:

Org, $$, #of Sales (as the tie breaker)

So, I need it to look for a specific Org Dept in Column B, rank the Dollar Amounts in column C, and if there are any ties, go to #Sales in column D and use the biggest number there to break the tie.

I've found 2 formulas..

This one was awesome for looking for the specifc Org Dept and only ranking those numbers that matched the designated Org Dept. (but this can't break any ties)..

IF(ISERROR(MATCH($O3,LARGE(IF($P$3:$P$53=R$2,$O$3:$O$53),ROW(INDIRECT("1:"&COUNTIF($P$3:$P$53,R$2)))),0)),"",MATCH($O3,LARGE(IF($P$3:$P$53=R$2,$O$3:$O$53),ROW(INDIRECT("1:"&COUNTIF($P$3:$P$53,R$2)))),0))

Then there's this one that broke ties, but can't recognize a specific Org Dept that I need..

=RANK($O3,$O$3:$O$53)+SUMPRODUCT(($O$3:$O$53=$O3)*($F$3:$F$53>$F3))

View 9 Replies
View Related