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

 

[LaurenHancy]

I have another questions you might ask me to post a new message, but you've all been so helpful. Hopefully it's an easy one for you.

Is there anyway I can calculate the number of months left until the 3rd or 4th year etc?

So if it a contract is in the 9th month, it would be 3 months remaining until 2nd year starts, if its 25 months, it would be 7 months until the new year, etc

Basically I have to work out RFL tax claw back, but it is paid annually so need to just work out the remining months in that year? I hope that makes sense?

Thanks for all your help

 

[Toadstool]

This worked amazingly. Thank you so much

You're welcome!
Thanks for the feedback.

 

[Toadstool]

I have another questions you might ask me to post a new message, but you've all been so helpful. Hopefully it's an easy one for you.

Is there anyway I can calculate the number of months left until the 3rd or 4th year etc?

So if it a contract is in the 9th month, it would be 3 months remaining until 2nd year starts, if its 25 months, it would be 7 months until the new year, etc

Basically I have to work out RFL tax claw back, but it is paid annually so need to just work out the remining months in that year? I hope that makes sense?

Thanks for all your help

I'm afraid I'm not following.
If the annual payment is based on contract start then I understand 9 months + 3 months = 12 months but 25 months is 2 years and one month so wouldn't year 3 start in another 11 months, rather than 7?

Take a look at this and let me know if it answers your question:

LaurenHancy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Contract Month	Months Remaining	Until Year		Contract Month	Months Remaining	Until Year		Contract Month	Months Remaining	Until Year		Contract Month	Months Remaining	Until Year
2	1	11	1		13	11	2		25	11	3		37	11	4
3	2	10	1		14	10	2		26	10	3		38	10	4
4	3	9	1		15	9	2		27	9	3		39	9	4
5	4	8	1		16	8	2		28	8	3		40	8	4
6	5	7	1		17	7	2		29	7	3		41	7	4
7	6	6	1		18	6	2		30	6	3		42	6	4
8	7	5	1		19	5	2		31	5	3		43	5	4
9	8	4	1		20	4	2		32	4	3		44	4	4
10	9	3	1		21	3	2		33	3	3		45	3	4
11	10	2	1		22	2	2		34	2	3		46	2	4
12	11	1	1		23	1	2		35	1	3		47	1	4
13	12	12	2		24	12	3		36	12	4		48	12	5
Sheet2

Cell Formulas
Range	Formula
N2:N13,J2:J13,F2:F13,B2:B13	B2	=12-(MOD(A2,12))
O2:O13,K2:K13,G2:G13,C2:C13	C2	=INT(A2/12)+1