RANK based on multiple criteria

[mikeymay]

I have a list of data that consists of 3 columns

Col A consists of values T1-T4
Col B consists of a region name (North American, Europe, etc)
Col c consists of a numeric value

I need to create a formula that will rank the above based on their value within their region with all T1 values being ranked first, then T2, etc.

So a T1 for North America with a value of 100 (being the highest value in the list for North America for all T1's) would be 1 and a T4 for North America for 100 rank would be the next number after all the T1-T3's had been ranked.


TIA

 

[barry houdini]

Hello Mikeymay,

Assuming data is in rows 2 to 100 (adjust as required) you can use this formula for ranking in D2 copied down:

=SUMPRODUCT(((5-RIGHT(A$2:A$100))*1000+C$2:C$100>(5-RIGHT(A2))*1000+C2)*(B$2:B$100=B2))+1

The 2 * 1000 values in the formula are a little arbitrary - this works assuming column C values won't exceed 999 - if they might exceed 999 then you need to change those 1000 values appropriately

 

[mikeymay]

Barry

Many thanks for this.......

I did manage to work out how to do this by using multiple countifs but this is a much simpler way of achieving what I need )

 

[mikeymay]

Barry

I have spent a bit of time trying to work out what this is doing and being honest, I'm struggling......

I need to add a further column into it which would be a company name.

Basically, it could be that there are 2 rows of data with the same T value, Region, and value but would need to be ranked differently due to a different company name.

Is this possible with the formula you provided?

TIA