Rank by Brand by Product

[som3on3_10]

Hello,

I need some assistance in ranking some data using multiple criteria. I have attached a sample. What I need to do is to rank the Product within each Brand based on their Sales. So for example under the "A" Brand I need to rank each Product based on sales volume, then under the "B" Brand I will need to do the same thing and so on and so forth. I need to basically populate column D on the attached automatically. I've searched around and found the SUMPRODUCT method, but it only works using one criteria. Any help would be appreciated.

Thanks!

[som3on3_10]

Slight correction on the attached file, instead of ranking sales in ascending order, I need it to rank in descending order so that the highest sales will have a ranking of 1, second highest ranking of 2, etc. Thanks again!

[npamcpp]

You can do that without any formula by using a pivot table. Pull Brand and Product into the row panel, pull Sales into the Values panel. In the Value Field Settings for the Sales field, click "Show Values As" then select "Rank smallest to largest" (or the other way around) and select "Product" as the base field.

See attached.

[som3on3_10]

You can do that without any formula by using a pivot table. Pull Brand and Product into the row panel, pull Sales into the Values panel. In the Value Field Settings for the Sales field, click "Show Values As" then select "Rank smallest to largest" (or the other way around) and select "Product" as the base field.

See attached.

That doesn't work. It ranks it only by Brand and changes the value of the Products to all 1. I need it to rank it by Product within the Brand. So let's say Brand ABC has 10 Products. Those 10 Products need to be ranked 1 to 10 based on sales volume. Then let's say Brand DEF has 20 Products. Those 20 Products need to be ranked 1 to 20 based on sales volume, etc.

[NBVC]

Do you mean?

=SUMPRODUCT(--($A$2:$A$10=A2),--(C2<$C$2:$C$10))+1

copied down.

[oeldere]

Maybe this way.

With a pivot table.

Also a formula to see the ranking (see the gold cells).

[npamcpp]

That doesn't work.

It does for me. I've added a few products to the data and attach the file again. What would you like to see differently?

[som3on3_10]

Do you mean?

=SUMPRODUCT(--($A$2:$A$10=A2),--(C2<$C$2:$C$10))+1

copied down.

This works! For some reason I thought that this formula wouldn't work, but after trying it it works perfectly. Maybe I was over complicating things. Thank you much!

[som3on3_10]

It does for me. I've added a few products to the data and attach the file again. What would you like to see differently?

I can't seem to see the "Rank Smallest to Largest" option when I click on the Value Field Settings for some reason. I was able to do it earlier at work, but I can't do it now that I'm at home. This wouldn't be an option only available in Excel 2010 would it? Because I have Excel 2010 at work, but 2007 at home. Weird...

[npamcpp]

That's not weird. Excel 2010 simply includes some new pivot functionality, so you cannot see that option in 2007.