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

Peter	25
John	99
Mike	36
Mike	25
John	48
Peter	99
Peter	2

<tbody>
</tbody>

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.