SUM IF / IF & OR statement :)

[LaurenHancy]

Hi Everyone!!!

I am trying to work out a statement using SUMIF and IF & OR.

Cell F23 is no. Months into agreement
Cell F24 is total commission made
Cell F25 is where I want my statement (Commission claw back depending on number of months into the agreement)

If cell F23 is >7 then add full commission amount. into field. If cell F23 is =>7 then add the following statements:

0-6 months = 100% of F24
7 months = 50% of F24
8 months = 40% of F24
9 months = 30% of F24
10 months = 20% of F24
11 months = 10% of F24
12 months = 0 of F24 (Leave at £0.00)

Is there a way to build this into a IF statement?

Thanks so much in advance

 

[Joe4]

If cell F23 is >7 then add full commission amount. into field. If cell F23 is =>7 then add the following statements:

I am guessing you mean F23 < 7 for your first one.

Try this:

=IF(F23<7,1,MAX(0.5-(F23-7)*0.1,0))*F24

 

[JimM]

Here's one way

=IF(F23<7,F24,CHOOSE(F23-6,50%,40%,30%,20%,10%,0)*F24)

 

[Joe4]

Here's one way

=IF(F23<7,G23,CHOOSE(F23-6,50%,40%,30%,20%,10%,0)*G23)

You formula is not working right for values <7 (returns 0 instead of 100% of value) and for values >13 (returns #VALUE error).

 

[JimM]

You formula is not working right for values <7 (returns 0 instead of 100% of value) and for values >13 (returns #VALUE error).

Yeah, think the issue was as I originally referenced the wrong cell (G23 should have been F24) now fixed

Fair enough on the >13 but OP only requested up to 12

 

[Toadstool]

Hi LaurenHancy,

Does this do what you want?

LaurenHancy.xlsx
	E	F
23	Months into agreement	10
24	Commission made	2000
25	Claw back	400
Sheet1

Cell Formulas
Range	Formula
F25	F25	=IF(F23<7,F24,((12-MIN(12,F23))/10)*F24)

 

[LaurenHancy]

Yeah, think the issue was as I originally referenced the wrong cell (G23 should have been F24) now fixed

Fair enough on the >13 but OP only requested up to 12 Thank you so much

 

[LaurenHancy]

Hi LaurenHancy,

Does this do what you want?

LaurenHancy.xlsx
	E	F
23	Months into agreement	10
24	Commission made	2000
25	Claw back	400
Sheet1

Cell Formulas
Range	Formula
F25	F25	=IF(F23<7,F24,((12-MIN(12,F23))/10)*F24)

This worked amazingly. Thank you so much

 

[Joe4]

I think the initial reply I made (up in post 2) should work too.
(You may have missed that one).

 

[LaurenHancy]

I think the initial reply I made (up in post 2) should work too.
(You may have missed that one).

Thank you so much, it worked a treat too! :D