# Ranking using SUMPRODUCT - Help!?

#### Chrisdoc1984

##### New Member
Hi,

Can anyone help me with this problem, its just a little beyond me...

I have been using the formula below to Rank a set of results

=IF(AND(EW2>0),SUMPRODUCT((U\$4:U\$99987=U2)*(A\$4:A\$99987=A2)
*(EW\$4:EW\$99987>0)*(DQ2 < DQ\$4:DQ\$99987)) +1,9999)<DQ\$4:dq\$99987))+1,9999)<dq\$4:dq\$3000))+1,9999)

EW contains employee "logged in hours"
U contains the employee department
A contains the week number (the weeks are stacked on top of each other)
DQ contains the Sales Per Hour

The formula above groups the employees into departments and ranks them based on their Sales per Hour as long as their logged in hours are greater than zero. The problem I have is that a lot of the employees have No Sales per Hour, meaning that about 50% of the data set are given the same ranking. I need these employees to then be ranked based on their logged in hours, so those who have done the most hours but still not sold anything are ranked lower.

Last edited:

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Can you post a good sample set of data, 10-20 rows, with the correct rankings manually entered for that sample set?

The ranges in your formula start at row 4 but you are referring also to single cells in row 2, so I'm not sure where the data starts but assuming it starts in row 4 (adjust as required) try this formula in that row copied down

=IF(EW4>0,COUNTIFS(A:A,A4,U:U,U4,DQ:DQ,">"&DQ4,EW:EW,">0")+1+IF(DQ4=0,COUNTIFS(A:A,A4,U:U,U4,DQ:DQ,0,EW:EW,">"&EW4)),9999)

You can do it with SUMPRODUCT too but I imagine COUNTIFS will be more efficient

Replies
1
Views
86
Replies
2
Views
207
Replies
1
Views
110
Replies
11
Views
586
Replies
9
Views
245

1,196,516
Messages
6,015,679
Members
441,915
Latest member
sm Hussaini

### 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