Filtered Rank using Sumproduct with no duplicates

[dgagliardi]

Need help. I have a set of data, company, department, sales. I want to uniquely rank sales within the department within the company. No criteria for tiebreakers other than where they are in the list, ie row 10 vs row 11, etc. I've used the SUMPRODUCT solution (column D), but I get inconsistent results and can't find a way to show tiebreakers as sequential numbers.

I don't want to use a helper columns if I can avoid it.

Alternatively, I would be okay with duplicates in the rank but the next value in the series would be the next number up.

ie
calculated desired
1 1
1 1
1 1
4 2

I'm using Office 2007 but can use 2010 if required.


any help is greatly appreciated.
D

Book1.xlsx

[azumi]

Put in E2 and copied down

=(SUMPRODUCT(($B$2:$B$11=B2)*($A$2:$A$11=A2)*($C$2:$C$11<C2))+1)+COUNTIFS($C$2:C2,C2,$B$2:$B2,B2,$A$2:A2,A2)-1

[Ali Kırksekiz]

Please Login or Register  to view this content.