rank based on multiple criteria

[ysherriff]

Hello all,

I have a table structure that I need to rank based on multiple criteria. The data table is setup where I have a list of hospital names with the name of their state and the number of discharges annually.

I need to rank the number of discharges for each hospital rather than the entire list. the data is already sorted by

ex.

hospital name state discharge rank
abbot al 50 1
abbot al 30 2
tcu tx 70 2
tcu tx 89 1
rice ca 100 1
ny nj 90 1
ny nj 40 2


I have attached sample subset.

thank you for your help.

 

[ysherriff]

here is a better layout:

• hospital state disch rank
• abbot al 50 1
• abbot al 30 2
• tcu tx 70 2
• tcu tx 89 1
• rice ca 100 1
• ny nj 89 1
• ny nj 40 2

 

[Somendra Misra]

Hi,

Can you see if this links helps you out,

http://chandoo.org/forum/threads/rank-calculation.20907/#post-126042

Regards,

 

[Somendra Misra]

here is a better layout:

• hospital state disch rank
• abbot al 50 1
• abbot al 30 2
• tcu tx 70 2
• tcu tx 89 1
• rice ca 100 1
• ny nj 89 1
• ny nj 40 2

For this sample try below formula also:

=COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2,$C$2:$C$8,">"&C2)+1

Regards,

 

[ysherriff]

let me try somendra. thank you

 

[ysherriff]

Thank you very much. it worked.

 

[Somendra Misra]

You are Welcome!!!

Regards,