How to sum and rank with multi criteria uniquely without duplicate? For excel 2019

[Sochen]

Hi All,

I would like to sum and rank categories below with multi criterial uniquely.
As you can the the column "Categories Ranking" is the result that I want to have. And before ranking the categories there will be sum of the same categories, Week no., and department also. Example, Apple in Week No 2, Department A, the total is 70$ because there are 2 Apples in the list. However, the result ranking of apple will appear only one, while another apple returns blank.

I hope you understand what I mean.
Thank you!

Test16.xlsx
	A	B	C	D	E	F
1		Criteria 1		Criteria 2	Criteria 3
2	Categories Ranking	Week No.	Date	Department	Categories	Sales
3	1	2	11-Jan-23	A	Apple	$ 50.00
4	3	2	11-Jan-23	A	Mango	$ 25.00
5	2	2	11-Jan-23	B	Banana	$ 15.00
6	2	2	11-Jan-23	A	Orange	$ 60.00
7		2	14-Jan-23	A	Apple	$ 20.00
8		2	14-Jan-23	A	Mango	$ 30.00
9	1	2	14-Jan-23	B	Watermelon	$ 20.00
10	1	2	14-Jan-23	C	Papaya	$ 75.00
11	2	2	15-Jan-23	C	Dragon fruits	$ 15.00
12	2	3	18-Jan-23	C	Cherry	$ 60.00
13	2	3	18-Jan-23	A	Apple	$ 30.00
14	1	3	18-Jan-23	C	Strawberry	$ 70.00
15	4	3	18-Jan-23	C	Grapes	$ 30.00
16	5	3	18-Jan-23	C	Durian	$ 20.00
17	1	3	18-Jan-23	A	Orange	$ 55.00
18	2	3	18-Jan-23	B	Banana	$ 35.00
19	3	3	18-Jan-23	C	Pineaple	$ 55.00
20		3	19-Jan-23	A	Orange	$ 10.00
21	1	3	19-Jan-23	B	Watermelon	$ 40.00
Sheet3

Cell Formulas
Range	Formula
B3:B21	B3	=ISOWEEKNUM(C3)

 

[Sochen]

Hi All,

I would like to sum and rank categories below with multi criterial uniquely.
As you can the the column "Categories Ranking" is the result that I want to have. And before ranking the categories there will be sum of the same categories, Week no., and department also. Example, Apple in Week No 2, Department A, the total is 70$ because there are 2 Apples in the list. However, the result ranking of apple will appear only one, while another apple returns blank.

I hope you understand what I mean.
Thank you!

Test16.xlsx
	A	B	C	D	E	F
1		Criteria 1		Criteria 2	Criteria 3
2	Categories Ranking	Week No.	Date	Department	Categories	Sales
3	1	2	11-Jan-23	A	Apple	$ 50.00
4	3	2	11-Jan-23	A	Mango	$ 25.00
5	2	2	11-Jan-23	B	Banana	$ 15.00
6	2	2	11-Jan-23	A	Orange	$ 60.00
7		2	14-Jan-23	A	Apple	$ 20.00
8		2	14-Jan-23	A	Mango	$ 30.00
9	1	2	14-Jan-23	B	Watermelon	$ 20.00
10	1	2	14-Jan-23	C	Papaya	$ 75.00
11	2	2	15-Jan-23	C	Dragon fruits	$ 15.00
12	2	3	18-Jan-23	C	Cherry	$ 60.00
13	2	3	18-Jan-23	A	Apple	$ 30.00
14	1	3	18-Jan-23	C	Strawberry	$ 70.00
15	4	3	18-Jan-23	C	Grapes	$ 30.00
16	5	3	18-Jan-23	C	Durian	$ 20.00
17	1	3	18-Jan-23	A	Orange	$ 55.00
18	2	3	18-Jan-23	B	Banana	$ 35.00
19	3	3	18-Jan-23	C	Pineaple	$ 55.00
20		3	19-Jan-23	A	Orange	$ 10.00
21	1	3	19-Jan-23	B	Watermelon	$ 40.00
Sheet3

Cell Formulas
Range	Formula
B3:B21	B3	=ISOWEEKNUM(C3)

Hi All,

Probably need any helper column to solve this problem?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to rank multi criteria by group uniquely?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Sochen]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to rank multi criteria by group uniquely?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Hello Sir,

My sincere apologies and well noted.
Have a great day Sir.