Top 10 Ranking from different and similar products

[awalhosen]

I want to Rank Top 10 Products along with figure from another sheet as below. The other sheet contains similar Products name in multiple category with the total from each category. I tried INDEX & LARGE formula, but it did not work well because Total figure would become bigger. Would appreciate your urgent assistance.

Sheet 1:

Sheet 2:

 

[Jamie McMillan]

Hello,

I hope I understand you properly.

Put a column next to the 'Purchase Amount' With the formula =d2 (if d2 is the first purchase amount.) Copy it down and delete the cells next to the totals.

Then you can use the LARGE function on that column.


You can hide that column; or just colour the text white.

Jamie

 

[awalhosen]

Hello,

I hope I understand you properly.

Put a column next to the 'Purchase Amount' With the formula =d2 (if d2 is the first purchase amount.) Copy it down and delete the cells next to the totals.

Then you can use the LARGE function on that column.


You can hide that column; or just colour the text white.

Jamie

Hi Jamie,

Thanks for your prompt reply.
I highly appreciate it. The problem is that I cannot change the Data. I could have been easier if I could remove it.
Is there any other ways I can do it using VLOOKUP SUMPRODUCT or SUMIF?

 

[Jamie McMillan]

Hello,

Here is a long formula that works.

=IF(AND(LARGE($Q$4:$Q$8,1)>LARGE($Q$10:$Q$14,1),LARGE($Q$4:$Q$8,1)>LARGE($Q$16:$Q$19,1)),LARGE($Q$4:$Q$8,1),IF(AND(LARGE($Q$10:$Q$14,1)>LARGE($Q$4:$Q$8,1),LARGE($Q$10:$Q$14,1)>LARGE($Q$16:$Q$19,1)),LARGE($Q$10:$Q$14,1),LARGE($Q$16:$Q$19,1)))

It just says, if the first group's largest number is greater than the other two groups display the first group's largest number, ELSE if the second group's largest number is greater than the other two groups display the second group's largest number ELSE display the third group's largest number.

I did it in column Q - so just move the ranges - copy it down and change for 2nd, 3rd, ... largest number.

Jamie