how to uniquely rank worst seller by group and multi criteria?

[Manith]

Hi All,

I would like to rank the below table of worst seller by group with multi criteria.
The first priority/condition to rank the worst seller is starting from "Qty sold", then "Week Launch", "Discount". The rank number needs to be uniquely without duplicating.
And if the Product that launch below 8 weeks will not count/rank as worst seller.

Thank you so much!

 

[Eric W]

For the Qty Sold, I assume the lower the number, the worse the seller? For week launch, is a lower number worse or better? For Discount, is a lower number worse or better?

 

[Manith]

For the Qty Sold, I assume the lower the number, the worse the seller? For week launch, is a lower number worse or better? For Discount, is a lower number worse or better?

Hi Eric, Yes, the low qty sold is the worse seller and lower week launch is the better, so higher week launch is the worst, then the lower number is the better, meaning higher discount is worst seller. And i would like to correct the rank for "Speaker" from Rank 3 to Rank 2 as it was duplicated. Thank you1

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
1
2	Group	Product	Qty Sold	Week Launch	Discount	Rank
3	A	iPhone	1	12	30%	3
4	A	iWatch	0	9	0%	1
5	A	Samsung	1	15	10%	2
6	B	TV	1	9	20%	3
7	B	Radio	1	16	20%	1
8	B	Speaker	1	16	0%	2
9	C	Aircon	0	18	0%	1
10	C	Refrigerator	0	3	0%
11	C	Fan	0	13	20%	2
12
Sheet18

Cell Formulas
Range	Formula
F3:F11	F3	=IF(D3<8,"",SUMPRODUCT(--((((1000-$C$3:$C$11)*100+$D$3:$D$11+$E$3:$E$11)*($A$3:$A$11=$A3)*($D$3:$D$11>=8))>=(1000-$C3)*100+$D3+$E3)))

This assumes the quantity sold will never be more than 999.

 

[Manith]

Try:

Book1
	A	B	C	D	E	F
1
2	Group	Product	Qty Sold	Week Launch	Discount	Rank
3	A	iPhone	1	12	30%	3
4	A	iWatch	0	9	0%	1
5	A	Samsung	1	15	10%	2
6	B	TV	1	9	20%	3
7	B	Radio	1	16	20%	1
8	B	Speaker	1	16	0%	2
9	C	Aircon	0	18	0%	1
10	C	Refrigerator	0	3	0%
11	C	Fan	0	13	20%	2
12
Sheet18

Cell Formulas
Range	Formula
F3:F11	F3	=IF(D3<8,"",SUMPRODUCT(--((((1000-$C$3:$C$11)*100+$D$3:$D$11+$E$3:$E$11)*($A$3:$A$11=$A3)*($D$3:$D$11>=8))>=(1000-$C3)*100+$D3+$E3)))

This assumes the quantity sold will never be more than 999.

Hi Eric,
I tried your formula and added one more condition, it worked. However, it created a duplicate rank number.
here is the formula: =IF(D6<8,"",SUMPRODUCT(--((((1000-$C$3:$C$490)*100+$D$3:$D$490+$E$3:$E$490)*($A$3:$A$490=$A6)*($B$3:$B$490=$B6)*($D$3:$D$490>=8))>=(1000-$C6)*100+$D6+$E6)))

Thank you!

 

[Eric W]

They have duplicate ranks because they have identical parameters. If you want something to break such ties, we can add the row number to the mix, but that's kind of arbitrary.