Help with sumproduct for mutliple criteria ranking with a tie breaker

Hi,

I've looked around multiple forums and tried to find a solution to my problem but can't make it work....any help would be appreciated!

I have a data set as follows:

A B C D E
User Department Sales Projects Rank
U UK 80 25 1
V Non UK 15 35 1
W UK 20 10 3
X UK 10 15 4
Y UK 80 20 2
Z Non UK 6 92 2

I want to rank the users within each department in terms of sales (so UK users - U, W, X, Y and Non Uk - V, Z have separate rankings). If their sales figures are the same (U,Y = 80), then they should be ranked on the value of their projects in column d (Hence U>Y).

I want all the rankings to appear in one column (as in column E - i've manually filled that in) becauseI have multiple sets of rankings, and multiple divisions so doing it over multiple columns, or using autosort won't work.

I've attached a workbook with the example (and the formula i've been using as well, which does not separate duplicates). Until now i've been using:

=SUMPRODUCT(($C3<=$C$3:$C$1002)*($B3=$B$3:$B$1002))

Thanks,
Shah

Perhaps:

=SUMPRODUCT(($C3<$C$3:$C$1002)*($B3=$B$3:$B$1002))+COUNTIFS(B$3:B3,B3,C$3:C3,C3)