Dynamic sort not working - Tie Breaker

Hello, I have attached a sample of my document for a visual... but basically I have this table with all 50 states and a few US territories and I have pulled the Total contract value and number of deals from another sheet using SUMIF and COUNTIF.. now I want to dynamically sort them by total contract value highest to lowest, but I am having trouble figuring out the tie breaker. I made the same table twice - have them ranked in the first table and am now using vlookup to put them in order 1 to 50


I am currently using a VLOOKUP, VLOOKUP in table 2 because I thought it may fix my tie breaker issue? but I am not exactly sure why I am using it twice
I am currently using RANK and COUNTIF in table 1 - but that is not breaking the ties - for example Arkansas and Colorado are both 1
I spent days trying to teach myself to use the SMALL function - epic failure

I was wondering if any of you may have a better idea of how I can get this dynamic rank to work

mY APOLOGIES, HERE IS THE DOCUMENT

Your formula in column A should be

=RANK($D3,$D$3:$D$62)+COUNTIF(D$3:D3,D3)-1

Thank you! Now I was wondering how I can insert my own tie breaker - I assume this tie breaker is just based on the one listed first in my original table... how would I make the tie breaker like this: if Total contract value is tied, then list the state with highest total contract value divided by number of deals = D/C = average contract value?

Could you explain what that is doing? If D3 is a number then display as D3+D3/C3*1000?

This also eliminates the need for adding the countif to the rank funciton.

E3 and filled down is
=IF(ISNUMBER(D3),D3+(1/C3),"")

A3 and filled down is
=RANK($E3,$E$3:$E$62)

Good point.

Awesome - works like a charm. I left the countif in there. Thanks for all of your help