Rank lowest to highest with duplicate values

I have a spreadsheet of 10 employees (column A) with a data validation list of products (column b). I created two different formulas in columns C & D to extract all of the digits from the product names in column A in attempt to setup a rank formula from lowest to highest $ amount. The problem is employees can have two different products or the same, but the formulas view them the same (ie $500-$20 & $500-$40 - formula views them as 500). I am searching for a rank formula solution that can differentiate products that have the same dollar amounts.

Employee	Products	    Formula 1	  Formula 2	 Rank on formula 1	  Rank on formula 2
Employee A   Feet $500-$40	      500	    500-40	       1	                  3
Employee C   Feet $500-$40	      500	    500-40	       1	                  3
Employee C   Pontiac $500-$20	      500	    500-20	       1	                  2
Employee D   USA Feet $2,000-100%    2000	    2000-100	       2	                  1
Employee E   USA Feet $2,000-100%    2000	    2000-100	       2	                  1
Employee F   Pontiac $500-$20	      500	    500-20	       1	                  2
Employee G   Pontiac $500-$20	      500	    500-20	       1	                  2
Employee H   Feet $500-$40	      500	    500-40	       1	                  3

Desired Result:

Employee	Products	             Rank
Employee A	Feet $500-$40	              2
Employee B	Feet $500-$40	              2
Employee C	Pontiac $500-$20	      1
Employee D	USA Feet $2,000-100%	      3
Employee E	USA Feet $2,000-100%	      3
Employee F	Pontiac $500-$20	      1 
Employee G	Pontiac $500-$20	      1
Employee H	Feet $500-$40	              2

Does this do what you want?

Formula:

=RANK(B2,$B$2:$B$9,1)+COUNTIF($B$2:B2,B2)-1

Try

Formula:

=RANK(B2,$B$2:$B$9,0)+COUNTIF(B2:$B$9,B2)-1

Originally Posted by Stuepef

I edited my OP to hopefully provide more detail of the issues I am running into with ranking the products.

Please don't do this again, as mentioned in post #6: instead, add extra or changed detail to a new post in the thread. There are about 15 edits to the opening post!!!

Would you include the expected results also and an explanation of the logic behind the "why" those are the results.

Additionally ... as a courtesy ... please don't edit posts after there have already been replies. It 'messes' with the thought flow of the thread and becomes difficult to know how responses relate to the thread.

C is ahelper column
In C2 then drag down.

=MID($B2,FIND("$",$B2)+1,FIND("-",$B2)-FIND("$",$B2)-1)+SUBSTITUTE(RIGHT($B2,LEN($B2)-FIND("-",$B2)),"$","")*10^-3

In F2 then drag down.

=IF(C2="","",SUMPRODUCT(($C$2:$C$9<C2)/COUNTIF($C$2:$C$9,$C$2:$C$9))+1)

Or in your attachment just change formula.

D2

Formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),FIND(" ",B2&" ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789"))),"$",""),"%",""),",",""),"-",".")+0