Actually i am not getting the proper rank which i am looking for in column F while using SUMPRODUCT formula, how can I get rank like i have shown in column F for different departments?
I have used this formula =SUMPRODUCT(($D$2:$D$100=D2)($E$2:$E$100>E2))+1 and its showing me
1,2,3,3,5,5,5,8,8 for the Finance & Audit instead it should be 1,2,3,3,4,4,4,5,5
can someone tell me where i am going wrong or what should be right formula to use*
>Solution :
Try this in cell F2
=SUMPRODUCT((E2<E$2:E$17)*($D$2:$D$17=D2)/COUNTIFS($D$2:$D$17,$D$2:$D$17,E$2:E$17,E$2:E$17))+1
And Fill Down!
