Calculation Winner by fulfilling multiple conditions

[arifgold]

Hi, I want to calculate the winner by fulfilling these following conditions. For example I gave a Winner list at the bottom of this page.

Conditions:
1. The Winner will be he, who achieved maximum Sales

2. Department "A" winners cannot exit 6

3. Department "B" winners cannot be more than 3 4. Department "c" winners cannot be more than 1 5. Group "X" winners cannot exit 6 6. Group "Y" winners cannot exit 3 7. Group "Z" winners cannot exit 1 ---------------------- Data: Group Department Sales (amount) z B 14500 x A 13500 y B 13000 x A 8567 x A 5000 y A 5000 y A 4750 x A 4000 x A 3750 z C 3309 z A 2000 z C 1905 x C 1500 x A 950 x B 900 x B 850 x A 850 y C 800 y A 750 z A 700 z B 700 z A 600 y A 500 y A 500 y A 400 y B 350 x A 300 z A 290 x A 275 x C 250 x B 250 x B 150 z B 100 <tbody> </tbody> ------------------------------------- Example based on above Data: RANK Group Department Sales Result 1 Z B 14,500 Winner 2 X A 13,500 Winner 3 y B 13,000 Winner 4 X A 8,567 Winner 5 Y A 5,000 Winner 6 Y A 4,750 Winner 7 X A 3,750 Winner 8 X C 1,500 Winner 9 X A 950 Winner 10 X B 900 Winner <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

Welcome to the Board!

I think you mean "exceed" instead of "exit". If so, I came up with this solution:

	A	B	C	D	E	F	G	H	I	J	K	L
1	Group	Department	Sales (amount)		Department	Max winners	Group	Max winners		Winners
2	z	B	14500		A	6	x	6		Group	Department	Amount
3	x	A	13500		B	3	y	3		z	B	14500
4	y	B	13000		C	1	z	1		x	A	13500
5	x	A	8567							y	B	13000
6	x	A	5000							x	A	8567
7	y	A	5000							x	A	5000
8	y	A	4750							y	A	5000
9	x	A	4000							y	A	4750
10	x	A	3750							x	A	4000
11	z	C	3309							x	C	1500
12	z	A	2000							x	B	900
13	z	C	1905									0
14	x	C	1500									0
15	x	A	950									0
16	x	B	900									0
17	x	B	850									0
18	x	A	850									0
19	y	C	800									0
20	y	A	750									0
21	z	A	700									0
22	z	B	700
23	z	A	600
24	y	A	500
25	y	A	500
26	y	A	400
27	y	B	350
28	x	A	300
29	z	A	290
30	x	A	275
31	x	C	250
32	x	B	250
33	x	B	150
34	z	B	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Array Formulas Cell Formula J3 {=IFERROR(INDEX($A$2:$A$34,SMALL(IF($C$2:$C$34=$L3,IF(COUNTIF($K$2:$K2,$B$2:$B$34)<SUMIF($E$2:$E$4,$B$2:$B$34,$F$2:$F$4),IF(COUNTIF($J$2:$J2,$A$2:$A$34)<SUMIF($G$2:$G$4,$A$2:$A$34,H$2:$H$4),ROW($A$2:$A$34)-ROW($A$2)+1))),COUNTIF($L$3:$L3,$L3))),"")} K3 {=IFERROR(INDEX($B$2:$B$34,SMALL(IF($C$2:$C$34=$L3,IF(COUNTIF($K$2:$K2,$B$2:$B$34)<SUMIF($E$2:$E$4,$B$2:$B$34,$F$2:$F$4),IF(COUNTIF($J$2:$J2,$A$2:$A$34)<SUMIF($G$2:$G$4,$A$2:$A$34,H$2:$H$4),ROW($A$2:$A$34)-ROW($A$2)+1))),COUNTIF($L$3:$L3,$L3))),"")} L3 {=MAX(IF(COUNTIFS($J$2:$J2,$A$2:A$34,$K$2:$K2,$B$2:$B$34,$L$2:$L2,$C$2:$C$34)=0,IF(COUNTIF($K$2:$K2,$B$2:$B$34)<SUMIF($E$2:$E$4,$B$2:$B$34,$F$2:$F$4),IF(COUNTIF($J$2:$J2,$A$2:$A$34)<SUMIF($G$2:$G$4,$A$2:$A$34,H$2:$H$4),$C$2:$C$34))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

My result table is a little different than your expected results, please check to see if it is correct. Also, I probably could have simplified the formulas if I'd assumed that the amounts are in sorted order, but I didn't assume that. Even so, probably easier in VBA.

Let me know if this helps.

 

[arifgold]

Welcome to the Board!

I think you mean "exceed" instead of "exit". If so, I came up with this solution:

	A	B	C	D	E	F	G	H	I	J	K	L
1	Group	Department	Sales (amount)		Department	Max winners	Group	Max winners		Winners
2	z	B	14500		A	6	x	6		Group	Department	Amount
3	x	A	13500		B	3	y	3		z	B	14500
4	y	B	13000		C	1	z	1		x	A	13500
5	x	A	8567							y	B	13000
6	x	A	5000							x	A	8567
7	y	A	5000							x	A	5000
8	y	A	4750							y	A	5000
9	x	A	4000							y	A	4750
10	x	A	3750							x	A	4000
11	z	C	3309							x	C	1500
12	z	A	2000							x	B	900
13	z	C	1905									0
14	x	C	1500									0
15	x	A	950									0
16	x	B	900									0
17	x	B	850									0
18	x	A	850									0
19	y	C	800									0
20	y	A	750									0
21	z	A	700									0
22	z	B	700
23	z	A	600
24	y	A	500
25	y	A	500
26	y	A	400
27	y	B	350
28	x	A	300
29	z	A	290
30	x	A	275
31	x	C	250
32	x	B	250
33	x	B	150
34	z	B	100

<tbody>
</tbody>

Sheet8​

Array Formulas Cell Formula J3 {=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$34,SMALL(<font color="Green">IF(<font color="Purple">$C$2:$C$34=$L3,IF(COUNTIF($K$2:$K2,$B$2:$B$34)<sumif($E$2:$E$4,$B$2:$B$34,$F$2:$F$4</sumif(),IF(COUNTIF($J$2:$J2,$A$2:$A$34)<sumif($G$2:$G$4,$A$2:$A$34,H$2:$H$4</sumif(),ROW($A$2:$A$34)-ROW($A$2)+1))),COUNTIF($L$3:$L3,$L3))),"")} K3 {=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$2:$B$34,SMALL(<font color="Green">IF(<font color="Purple">$C$2:$C$34=$L3,IF(COUNTIF($K$2:$K2,$B$2:$B$34)<sumif($E$2:$E$4,$B$2:$B$34,$F$2:$F$4</sumif(),IF(COUNTIF($J$2:$J2,$A$2:$A$34)<sumif($G$2:$G$4,$A$2:$A$34,H$2:$H$4</sumif(),ROW($A$2:$A$34)-ROW($A$2)+1))),COUNTIF($L$3:$L3,$L3))),"")} L3 {=MAX(<font color="Blue">IF(<font color="Red">COUNTIFS($J$2:$J2,$A$2:A$34,$K$2:$K2,$B$2:$B$34,$L$2:$L2,$C$2:$C$34)=0,IF(<font color="Green">COUNTIF($K$2:$K2,$B$2:$B$34)<sumif(<font color="Purple">$E$2:$E$4,$B$2:$B$34,$F$2:$F$4</sumif(),IF(COUNTIF($J$2:$J2,$A$2:$A$34)<sumif($G$2:$G$4,$A$2:$A$34,H$2:$H$4</sumif(),$C$2:$C$34))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

My result table is a little different than your expected results, please check to see if it is correct. Also, I probably could have simplified the formulas if I'd assumed that the amounts are in sorted order, but I didn't assume that. Even so, probably easier in VBA.

Let me know if this helps.

---------------------------------------------------

Dear Eric,

Thank you for your quick reply. The formula is showing error. please help.

 

[arifgold]

Dear Eric,

Thank you for your quick reply. The formula is showing error. Can you please provide the correct formulas.

 

[arifgold]

Dear Eric,

Thank you for your quick reply. The formula is showing error. Can you please provide the correct formulas. and Yes, I need the result in order largest to smallest.

 

[Marcelo Branco]

If acceptable I would create a Helper column to use simpler formulas.

Something like this

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

1​

Group​

Department​

Sales​

Dept/Group​

Max winners​

Rank​

Group​

Department​

Sales (amount)​

Result​

Helper​

Count​

2​

z​

B​

14500​

A​

6​

1​

z​

B​

14500​

Winner​

Winner1​

10​

3​

x​

A​

13500​

B​

3​

2​

x​

A​

13500​

Winner​

Winner2​

4​

y​

B​

13000​

C​

1​

3​

y​

B​

13000​

Winner​

Winner3​

5​

x​

A​

8567​

x​

6​

4​

x​

A​

8567​

Winner​

Winner4​

6​

x​

A​

5000​

y​

3​

5​

x​

A​

5000​

Winner​

Winner5​

7​

y​

A​

5000​

z​

1​

6​

y​

A​

5000​

Winner​

Winner6​

8​

y​

A​

4750​

7​

y​

A​

4750​

Winner​

Winner7​

9​

x​

A​

4000​

8​

x​

A​

4000​

Winner​

Winner8​

10​

x​

A​

3750​

9​

x​

C​

1500​

Winner​

11​

z​

C​

3309​

10​

x​

B​

900​

Winner​

12​

z​

A​

2000​

13​

z​

C​

1905​

14​

x​

C​

1500​

Winner9​

15​

x​

A​

950​

16​

x​

B​

900​

Winner10​

17​

x​

B​

850​

18​

x​

A​

850​

19​

y​

C​

800​

20​

y​

A​

750​

21​

z​

A​

700​

22​

z​

B​

700​

23​

z​

A​

600​

24​

y​

A​

500​

25​

y​

A​

500​

26​

y​

A​

400​

27​

y​

B​

350​

28​

x​

A​

300​

29​

z​

A​

290​

30​

x​

A​

275​

31​

x​

C​

250​

32​

x​

B​

250​

33​

x​

B​

150​

34​

z​

B​

100​

35 ​

<tbody>
</tbody>

Helper column (gray area)
Formula in N2 copied down
=IF(A2="","",IF(OR(COUNTIFS(A$1:A1,A2,N$1:N1,"Winner*")=VLOOKUP(A2,E$2:F$7,2,0),COUNTIFS(B$1:B1,B2,N$1:N1,"Winner*")=VLOOKUP(B2,E$2:F$7,2,0)),"","Winner"&COUNTIF(N$1:N1,"?*")))

Formula in O2 to count the winners
=COUNTIFS(N:N,"Winner*")

Formula in H2 copied down
=IF(O$2>=ROWS(I$2:I2),ROWS(I$2:I2),"")

Formula in I2 copied across till K2 and down
=IF($H2="","",INDEX(A:A,MATCH("Winner"&$H2,$N:$N,0)))

Formula in L2 copied down
=IF(H2="","","Winner")

Hope this helps

M.

 

[arifgold]

Dear Marcelo ,

WOW!!! you are a Genius. Thanks a lot. I will back to you if I need further help on this.

Thank you so much.

Best regards,

Arif

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[arifgold]

You are welcome. Glad to help

M.

Dear Marcelo,

Good day!

1. If we change the "Sales" amount the formula is not showing the correct result.

2. After input all the sales amount and before calculation the Winner, we need a table to sort the Sales amount largest to smallest order.

Please Help!

Regards,

 

[Marcelo Branco]

Yes, the formula works only if the Sales are sorted accordingly.
You can do this by selecting the data and going to Data, Sort by Sales column, largest to smallest.

M.