Ranking by multiple criterias

[NabMo]

Good day

i am administering a competition in excel. the participants are split per region and each one has a sales figure and sales target. basically if you make your target, you get a prize.

however the salesman with the highest value above target per region will get ad additional prize if they won the initial prize.

What i have currently:

• i have identified all the initial prize winners based on the sales figure and target.
• in a new column i inserted an IF statement so that it shows the difference between sales figure and target only if the target was met.

i am struggling to find a formula that only ranks the values displayed per region.

please help.

thanks

 

[wideboydixon]

Use SUMPRODUCT:

Book1
	A	B	C
1	REGION	VALUE	RANK IN REGION
2	A	10	1
3	B	9	1
4	C	8	2
5	A	7	3
6	B	6	3
7	C	10	1
8	A	9	2
9	B	8	2
10	C	7	3
Sheet1

Cell Formulas
Range	Formula
C2	=1+SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10>$B2))

WBD

 

[NabMo]

thanks for the response. i tried that but there is another criteria, only rank if the salesman qualified for the initial prize.

i did manage to get the result i want using the following: =IF(R4=3000,1+COUNTIFS(E$4:E$39,$E4,$Y$4:$Y$39,">"&$Y4),"")

column R being the prize money for making target
column E being the region
column Y being the variance of sales to target only if column R is populated.

thanks again.