Ranking with multi criteria

[jimblimm]

My Setup

[TABLE="width: 311"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]COUNT[/TD]
[TD]ATTENDANCE [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jake[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]536[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]709[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]betty[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]357[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jack[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1438[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]trevor[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1240[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 311"]
<TBODY>[TR]
[TD]name
[/TD]
[TD]COUNT
[/TD]
[TD]ATTENDANCE
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jake
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]536
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sue
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]709
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]betty
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]357
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jack
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]1438
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]trevor
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]1240
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

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!