Multiple Ranking Criteria

**Bullfn33** · 06-10-2007, 04:52 PM

I am trying to rank a list of names based off of more than one column. I would like to rank the list of names down column B based on column D AND F. Can this be done?

Thank you!

**daddylonglegs** · 06-10-2007, 05:57 PM

Do you mean that the ranking should be based on column D with F as tie-break or do you want to rank on the sum of D and F or something else?

**Bullfn33** · 06-10-2007, 06:06 PM

My bad, I should have been a little more clear. I do want to rank it based on D and then F as the tie breaker. Sorry about that.

**daddylonglegs** · 06-10-2007, 06:32 PM

OK assuming that highest is ranked 1 then this should do what you want in row 1 copied down, extend ranges as necessary

=RANK(D1,$D$1:$D$10)+SUMPRODUCT(--($D$1:$D$10=D1),--($F$1:$F$10>F1))

**Bullfn33** · 06-10-2007, 06:52 PM

you are awesome!

**angiekm** · 06-13-2007, 12:11 PM

Hi Daddylonglegs,

I like your solution. I gave it a try here at work. I was wondering, though, what does the "--" designate in front of each range in the sumproduct portion of the formula?

Thanks!

**daddylonglegs** · 06-13-2007, 03:58 PM

This part of the formula

$D$1:$D$10=D1

returns an array of TRUE/FALSE values but to count these they need to be converted to 1/0 values - that's what -- does. I could have used +0 or *1 to do the same, essentially any mathematical operation that doesn't change the value.

Another alternative syntax is

=SUMPRODUCT(($D$1:$D$10=D1)*($F$1:$F$10>F1))

see this link for more

http://www.xldynamic.com/source/xld....CT.html#format