Ranking using SUMPRODUCT - Help!?

[Chrisdoc1984]

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)

 

[jbeaucaire]

Can you post a good sample set of data, 10-20 rows, with the correct rankings manually entered for that sample set?

 

[barry houdini]

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