Ranking using SUMPRODUCT - Help!?

Chrisdoc1984

New Member
Joined
Apr 19, 2013
Messages
1
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.

Can anyone help please??</DQ$4:dq$99987))+1,9999)<dq$4:dq$3000))+1,9999)
 
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top