Help with sumproduct for mutliple criteria ranking with a tie breaker

[sshahils]

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:

Please Login or Register  to view this content.

Thanks,
Shah

[NBVC]

Perhaps:

Please Login or Register  to view this content.

[sshahils]

Thanks, this worked for me!