# Using Sumproduct Unable to remove duplicate ranking

#### yaetis

##### New Member
 A Manager B Employee C Salary D Comparative Ranking John Employee 1 10000 2 John Employee 2 10000 2 Rob Employee 3 35000 1 Rob Employee 1 0 3 Rob Employee 2 0 3 Smith Employee 1 60002 1 Smith Employee 3 50000 2 Smith Employee 2 0 3

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I want to get a comparative Ranking within each Manager,So for example , I wan to check with John, how his employees are ranked basis on Salary they are getting, For some record the Salary is 0 as well.
For Column D , i have used flat Sumproduct , =SUMPRODUCT(--(A3=\$A\$3:\$A\$10),--(C3<=\$C\$3:\$C\$10)), but this doesn't take care of Duplicates. I have tried various flavours using , Index as well, But no Luck as well.

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
d2 =COUNTIFS(\$A\$2:\$A\$9,A2,\$C\$2:\$C\$9,">"&C2)+COUNTIFS(A\$2:A2,A2,C\$2:C2,C2)

 Manager Employee Salary Comparative Ranking John Employee 1 10000 1 John Employee 2 10000 2 Rob Employee 3 35000 1 Rob Employee 1 0 2 Rob Employee 2 0 3 Smith Employee 1 60002 1 Smith Employee 3 50000 2 Smith Employee 2 0 3

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

d2 =COUNTIFS(\$A\$2:\$A\$9,A2,\$C\$2:\$C\$9,">"&C2)+COUNTIFS(A\$2:A2,A2,C\$2:C2,C2)

 Manager Employee Salary Comparative Ranking John Employee 1 10000 1 John Employee 2 10000 2 Rob Employee 3 35000 1 Rob Employee 1 0 2 Rob Employee 2 0 3 Smith Employee 1 60002 1 Smith Employee 3 50000 2 Smith Employee 2 0 3

<tbody>
</tbody>

Works like a Charms, I was so dumbStuck with Sumproduct , totally ignored the awesomeness of Countif..
Thanks a lot !

Replies
0
Views
174
Replies
0
Views
245
Replies
3
Views
1K
Replies
5
Views
1K
Replies
3
Views
386

1,207,011
Messages
6,076,145
Members
446,187
Latest member
LMill

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back