RANK Formula with Criteria

[hrayani]

Hello Friends,

I need a Rank formula based on criteria

Here is a sample data (having Names in A2:A8 & Qty in D2:D8)

NAMES Qty
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Peter[/TD]
[TD="width: 64, align: right"]25[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]99[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]99[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

Required names (rank wise) based on sum of Quantites in Column D

In this case answer should be

John (coz john has the maximum qty of 147)
Peter (then peter with a qty of 126)
Mike (then Mike with a qty of 61)

Any help would be appreciated.

Regards,

Humayun

 

[aqeelnokia99]

i will teach u how to use pivot table
1st select all sheet click insert =>click pivot table

http://prntscr.com/m06lf6
http://prntscr.com/m06m80
http://prntscr.com/m06mp8
after this make sheet value mode by select copy pres control +alt +v
http://prntscr.com/m06njy
now short data by larg to smallest

 

[aqeelnokia99]

i will teach u how to use pivot table
1st select all sheet click insert =>click pivot table

http://prntscr.com/m06lf6
http://prntscr.com/m06m80
http://prntscr.com/m06mp8
after this make sheet value mode by select copy pres control +alt +v
http://prntscr.com/m06njy
now short data by larg to smallest

 

[hrayani]

Both the formulas are working ok for me. If possible upload file

Hi kvsrinivasamurthy ,

I cant upload the file... but anyways i will stick to the first solution with the helper column as it is very fast as compared to the 2nd solution.
I will not be using the second code even if it works coz it takes a lot of time to calculate.

Thanks

 

[hrayani]

i will teach u how to use pivot table
1st select all sheet click insert =>click pivot table

http://prntscr.com/m06lf6
http://prntscr.com/m06m80
http://prntscr.com/m06mp8
after this make sheet value mode by select copy pres control +alt +v
http://prntscr.com/m06njy
now short data by larg to smallest

Hi Aqeel,

I will have to see if i can use pivot table as there are also other formulas which are dependent on required result.
But surely i will have a look into it once i am done with the current work.

Regards,

Humayun

 

[kvsrinivasamurthy]

Thanks for feed back.