Meet more than one criteria then calculate %

[LJH]

I need a formula to calculate the following:
Sales of 1st person >=£500
Sales of 2nd person >=£250
Sales of 3rd person >=£250
If all conditions met, then 1st person receives a bonus on the 2nd & 3rd sales. If sales criteria is only met by 1 person, then bonus only calculated on those individual sales.
In this example, no bonus is due as 1st person has not achieved £500

229.40

802.15

71.00

This one, however, does:

630.91

274.95

Hope that makes sense!

 

[Oeldere]

500​	229,40​		1​
250​	802,15​	bonus
250​	71,00​

C1 =If(B1>A1,"bonus","") and drag down.
D1 =Countif(C1:$C$3,"bonus")

 

[CA_Punit]

Deleted

Answer as stated above.

 

[LJH]

Thanks but instead of inserting the word bonus, I need it to calculate a %.
Do I have to have extra columns showing 500, 250, etc., or can it be incorporated in the formula?

 

[CA_Punit]

Can you be more specific and provide us details of expected result in an excel file using xl2bb

 

[Dave Patton]

Your description is not complete. Please provide a clear example with expected results with XL2BB
Try the following and edit it to meet your requirements.
Try using Excel's Data Evaluate Formula to review the formula.

T202007b.xlsm
	A	B	C	D	E
1	Person	Threshold	Actual	Bonus 12%	or
2	Person 1	500.00	630.91	739.61	739.61
3	Person 2	250.00	274.95	383.65	383.65
4	Person 3	250.00	125.00	0.00	0.00
5
1e

Cell Formulas
Range	Formula
D2:D3	D2	=ROUND((SUMPRODUCT(--($C$2:$C$4>=$B$2:$B$4))>1)*SUMPRODUCT(--($C$2:$C$4>=$B$2:$B$4),$C$2:$C$4)*$D$1+C2,2)
E2	E2	=AND(C2>B2,OR(C3>B3,C4>B4))*(SUMPRODUCT(--($C$2:$C$4>=$B$2:$B$4),$C$2:$C$4)*$D$1+C2)
E3	E3	=AND(C3>B3,OR(C4>B4,C2>B2))*(SUMPRODUCT(--($C$2:$C$4>=$B$2:$B$4),$C$2:$C$4)*D1+C3)
D4	D4	=(C4>B4)*ROUND((SUMPRODUCT(--($C$2:$C$4>=$B$2:$B$4))>1)*SUMPRODUCT(--($C$2:$C$4>=$B$2:$B$4),$C$2:$C$4)*$D$1+C4,2)
E4	E4	=AND(C4>B4,OR(C2>B2,C3>B3))*(SUMPRODUCT(--($C$2:$C$4>=$B$2:$B$4),$C$2:$C$4)*D1+C4)