Multiple rankings w/n a single column?

[kbrownk]

I have 4 different types of values all found w/n a single column. I'd like to use another column that ranks each type independent of each other. That way, I'd have 4 different values of Rank = 1, etc.

For example:
Col A gives the type of value 1 through 4:
A1: 1
A2: 2
A3: 2
A4: 4
A5: 3
A6: 1
A7: 4
A8: 3
A9: 3

Col B gives their associated values I'd like ranked:
B1: 10
B2: 3
B3: 8
B4: 4
B5: 6
B6: 26
B7: 15
B8: 5
B9: 2

Col C I'd like to rank the values but only w/n type (larger values rank lower):
C1: 2
C2: 2
C3: 1
C4: 2
C5: 1
C6: 1
C7: 1
C8: 2
C9: 3

What I tried for Col C was for example: C1=if(A1=1,rank(B1,B:B,0),if(A1=2,rank(B1,B:B,0),if(A1=3,rank(B1,B:B,0),if(A1=4,rank(B1,B:B,0),"")))

Of course the issue is that it still ranks based on all values regardless of type. Indeed, even if I only have C1=if(A1=1,rank(B1,B:B,0),"") it will only return a ranking at A1 if A1 does = type 1, but it still bases the rank off all values.

The obvious solution is to split stuff up in multiple columns but my worksheet will be massive if I do so for reasons unexplained here.

Thanks for any suggestions!
kbrownk

 

[marka87uk]

Try this, where A2:A11 is your types and B2:B11 is the values you want ranked.

=SUMPRODUCT(--(A$2:A$11=A2),--($B$2:$B$11>$B2))+1

You should be able to adjust the formula accordingly to your needs.

 

[kbrownk]

Thanks, this is altogether unfamiliar to me as I've never used sumproduct or whatever -- is, so I'm going to have to do some research, but it seems that sumproduct i suggested for lots of things so I better try and understand this.

kbrownk