Ranking with multi criteria

[jimblimm]

My Setup

name	COUNT	ATTENDANCE
jake	11	536
sue	9	709
betty	10	357
jack	9	1438
trevor	11	1240

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

i am trying to get the ranking based on
first the count, then the attendance,

the highest of the count should be first,

but the lowest of the attendance should be second

in the rank should be

1.jake
2.trevor
3. betty
4.sue
5.jack

 

[T. Valko]

My Setup

name	COUNT	ATTENDANCE
jake	11	536
sue	9	709
betty	10	357
jack	9	1438
trevor	11	1240

<TBODY>
</TBODY>

i am trying to get the ranking based on
first the count, then the attendance,

the highest of the count should be first,

but the lowest of the attendance should be second

in the rank should be

1.jake
2.trevor
3. betty
4.sue
5.jack

Try this...

Book1
	A	B	C	D
1	Name	Count	Attend	Rank
2	jake	11	536	1
3	sue	9	709	4
4	betty	10	357	3
5	jack	9	1438	5
6	trevor	11	1240	2
Sheet1

This formula entered in D2 and copied down:

=RANK(B2,B$2:B$6)+SUMPRODUCT(--(B2=B$2:B$6),--(C2>C$2:C$6))

 

[Matty]

Try:

=RANK(B2,B$2:B$6,0)+SUMPRODUCT(--(B$2:B$6=B2),--(C$2:C$6< C2))

<c2))[ code]
<c2))
<c2)[ code]

Matty</c2)[></c2))
</c2))[>

 

[MickG]

Perhaps, sot by "Count" decending , then "Attendance" ascending

 

[jimblimm]

PERFECT!!!! i been trying to figure it out for hours

 

[T. Valko]

PERFECT!!!! i been trying to figure it out for hours

Good deal. Thanks for the feedback!