Assume your data looks like this for the comparison of "Best A, No B":

...........A.............B
1.......61............10
2.......57................
3.......95................
4.......44............50
5.......65................

Enter this formula in C1 and copy down to C5:

=IF(B1,"",SUMPRODUCT(--(B$1:B$5=""),--(A1<A$1:A$5))+1)

Will return these results:

...........A.............B............C
1.......61............10.............
2.......57...........................3
3.......95...........................1
4.......44............50.............
5.......65...........................2

Biff

"gmunro" wrote in message
oups.com...
Hello,

I currently am using a rank formula based on two criteria followed by a
sort in a macro.
Basically, I have built this to rank my sales for Best Apple Sales but
No Banana Sales Or Best Banana Sales but No Cherry Sales.
To do this I have dedicated many columns and I am trying to reduce the
need as I have 8 criteria and all the possible combinations
Best A, No B
Best A No C,
Best A, No D, etc

As a trial that didn't work, I put the criteria into two vlookup
charts, one called BestChart, the other NoChart

Here is a formula I tried:
=IF(AND(VLOOKUP(BK21,BestChart,2,FALSE),VLOOKUP(BK 26,NoChart,2,FALSE)),RANK(VLOOKUP(BK21,BestChart,3 ,FALSE),VLOOKUP(BK21,BestChart,4,FALSE),0),"")

Can anyone suggest anotehr way to do this?

Glen