Ranking by sub group

**luke11111** · 02-22-2013, 06:10 AM

Im new to this and im trying to rank by sub group.

A B C
187-bbn 700 pads
187-bbd 330 pads
188-een 150 helmets
111-ssn 111 pads
119-qjw 122 helmets
119-wwe 150 wristwear

I need to rank each product code (A) within each sub group (C) so I would get top ten pads, or top ten helmets.
The worksheet is large. Can anyone help? thanks

**dilipandey** · 02-22-2013, 07:56 AM

Hi Luke,

welcome to the forum.

As an option, remove (using text-to-column), the word "Pads" from 700 (do it for entire column) and then you can sort data as descending order, first by column A and then by column C and then your top 10 results would be there on top

Regards,
DILIPandey
<click on below * if this helps>

**luke11111** · 02-22-2013, 08:22 AM

Book2.xlsxhello dilipandey,
thank you for helping but there are 146 different sub groups (pads, helmets, stickers etc...) in column C.
I need to rank each product by its code (column A) within its category (C) by its total sales (column B)

so i would end up with one formula that could sort and rank each sub group category.
Ive attached a small example. the worksheet contain 45000+ lines.
Thank you

**samba_ravi** · 02-22-2013, 08:39 AM

try this formula in column d
=SUMPRODUCT(($A$2:$A$71=$A2)*($C$2:$C$71=C$2)*($B$2:$B$71>=$B2))

**luke11111** · 02-22-2013, 08:43 AM

hello and thank you. however it returns an #N/A.

**luke11111** · 02-22-2013, 11:06 AM

That sounded a bit ungrateful and it wasnt supposed to. I have fixed the problem
=SUMPRODUCT(--($C$1:$C$7302=C1),--(B1<$B$1:$B$7302))+1
Thanks again for everyones help