Ranking based on 3 conditions

I have a ranking program that has a tie-breaker situation. It is based on multiple columns on the spreadsheet. Since multiple teams can have ties, I need to use all the conditions to separate them and determine a value based on each tie.

I have attached my spreadsheet.
Column C = shows ties
Column D = rank
Column E = tie-breaker number

Trying to Break the tied ranks using a tie-breaker.

If Column C=2, then look at Col D and find the duplicate. Then look at Col E to find out which number is the lowest. The lower number, subtract 0.5 from ColumnD. If they are equal, add 0 (and we'll go on to tie-breaker #2). If it is the higher number, add 0.5.

If Col C=3, then look at D to find the other 2 duplicates. Then look at E to find out which number is the lowest. The lower number, subtract 1 from ColumnD. The middle number stays the same, add 0. If it is the higher number, add 1.

I can't seem to get my formulas to look at each column with IF & AND statements. I also wonder if maybe it can't be done in a formula and needs to be in a macro. Any help is greatly appreciated.

Hi, I have a (rather clumsy, I'm afraid) suggestion. I made separate formulaes for split 2 and 3. (col K and L), with final result in col J.

If you decide to use my solution, take note that it must be modified somehow to handle when ties =3 have identical values in column E.

PS I have marked 3 cells red where I believe your calculations are not according to the rules you set up.

PPS Without succsess, I tried in all ways I could think of to use RANK in an array-formula. That would have opened up for a much better solution...

Thank you for your help!! I appreciate your willingness to take a stab at it. I will try it tonight.

Your answer was perfect. I found that I had an error in my original to you. Once I solved that issue, the formulas work perfectly. Thank you so much for all your help. Here is the spreadsheet with the problem solved.

Thank you very much for your help!!

As far as I can see, the sumproduct-solution doesn't give the right answer when ties = 3 (based on the rules set up in row 18). It does however return results equal to those manualy entered in column H.

The SUMIFS-solution however, seems to give the results (similar to mine) that follow from the rules set up.

No, you were right DO.
My comment was based on applying your solutions in the original workbook. I did not realize until after I commented that the example had some errors in column D. Your solution works brilliant in the new and correct workbook. Sorry for the "false alarm":-)

In making an Excel 97-2003 compatible version, I used the formula below with great success:
J3:
=1+SUMPRODUCT(--($B$3:$B$14+$E$3:$E$14/1000000<$B3+$E3/1000000))
copied down

They've decided to add a tie-breaker that includes total points instead of rank. In this case the total points being high would lead to a higher rank with 1 being the highest. Is there a way to alter the formula below to accomodate the higher points value to receive the higher rank? See attached.

Thanks so much for your help. The program was working great until this change and I can't quite figure out how to reverse it.