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*

CodePudding user response：

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!