RANK based on selling multiple items in different criteria (Excel 2016)

[Deepakchoudhary]

Friends, Purpose of required formula ( in column H) to know.. which "SHOP NAME" is ranking as per highest selling in column "SALE24" for each ITEM (Bread, Drink, Fruit) separately. Ranking required for separate STATE (A, B) and only for "OLD" in column CLASS. I am using Excel 2016 and I have entered the results manually in column H, as required through the formula.

(Data in below table as in A1 : H25)

SHOP NAME	ITEM	State	Manager	CLASS	TARGET	SALE24	RANK
CITY-1	FRUIT	A	M1	NEW	72​	75
CITY-1	DRINK	A	M1	NEW	64​	65
CITY-1	BREAD	A	M1	NEW	12​	8
CITY-2	FRUIT	A	M1	OLD	62​	68	1
CITY-2	DRINK	A	M1	OLD	31​	32	2
CITY-2	BREAD	A	M1	OLD	86​	92	1
CITY-3	FRUIT	B	M1	OLD	63​	59	2
CITY-3	DRINK	B	M1	OLD	71​	77	1
CITY-3	BREAD	B	M1	OLD	87​	90	2
CITY-4	FRUIT	B	M1	OLD	84​	91	1
CITY-4	DRINK	B	M1	OLD	58​	59	3
CITY-4	BREAD	B	M1	OLD	87​	87	3
CITY-5	FRUIT	A	M2	OLD	10​	13	3
CITY-5	DRINK	A	M2	OLD	20​	26	3
CITY-5	BREAD	A	M2	OLD	28​	30	2
CITY-6	FRUIT	B	M3	NEW	32​	33
CITY-6	DRINK	B	M3	NEW	30​	34
CITY-6	BREAD	B	M3	NEW	83​	80
CITY-7	FRUIT	A	M2	OLD	75​	71	2
CITY-7	DRINK	A	M2	OLD	69​	75	1
CITY-7	BREAD	A	M2	OLD	11​	14	3
CITY-8	FRUIT	B	M3	OLD	56​	55	3
CITY-8	DRINK	B	M3	OLD	66​	67	2
CITY-8	BREAD	B	M3	OLD	89​	91	1

 

[hagia_sofia]

Hello, would this work for you:

=IF(E2="NEW","",COUNTIFS($E$2:$E$25,E2,$B$2:$B$25,B2,$C$2:$C$25,C2,$G$2:$G$25,">"&G2)+1)

 

[Deepakchoudhary]

Hello, would this work for you:

=IF(E2="NEW","",COUNTIFS($E$2:$E$25,E2,$B$2:$B$25,B2,$C$2:$C$25,C2,$G$2:$G$25,">"&G2)+1)

Dear

hagia_sofia,​

Thanks for your support and reply. It is working well as required. Thanks Thank Thanks...

 

[hagia_sofia]

That is good to hear, we are glad to help.