Date and if formulas

[michyb]

Hi there brains trust! Hope you are all well
I have a question involving dates and lookups/if formulas - clearly i am doing something wrong as i cant get the right answer
I am needing to calculate commissions monthly and include previous months paid and not paid in columns following but i cant seem to get the formulas to work.
Clearly I am doing something wrong and really need some advice have spent 2 weeks on this already!
I have colour coded for ease of following the spreadsheet (have removed names for obvious reasons) and formulas but if someone can see a glaringly obvious error please could you help.

Have an awesome day all

excel commission help1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2
3		October 2020					Sales need to be closed within the month to be paid commission in that month, else they are paid in month finalised
4
5		Date	Invoice No.	Customer PO No.	Customer Name	Amount	Amount Due	Status	Month end	Date Closed (date paid)	Salesperson		salesperson	invoice no	commiss	paid	not paid
6
7		3/10/2020	1148567	2146566/VO472895		2834	0	Closed	44136	11/12/2020				1148567	n	0	2834		0
8		5/10/2020	1148611	2146539		2557.5	0	Closed	44136	11/12/2020				1148611	y	#N/A	#N/A		0
9		27/10/2020	1148683			295	-5	Credit	44136					1148683	n	0	295		0
10		26/10/2020	1148690	2137991		6882.7	0	Closed	44136	11/12/2020				1148690	y	#N/A	#N/A		0
11		26/10/2020	1148728	2161436		7810	0	Closed	44136	11/12/2020				1148728	n	0	7810		0
12
13							IF($I12=$B$7,,IF($J12<$B8,"n","y"))
14
15							IF$O12="n",,VLOOKUP($N12,$C$11:$H$152,4))
16
17							IF($O12="y",VLOOKUP($N12,$C$11:$H$152,4),IF($O12="n",F12))
18
Sheet1

Cell Formulas
Range	Formula
N7:N11	N7	=C7
O7:O11	O7	=IF($I7=$B$7,,IF($J7<$B3,"n","y"))
P7:P11	P7	=IF($O7="n",,VLOOKUP($N7,$C$11:$H$152,4))
Q7:Q11	Q7	=IF($O7="y",VLOOKUP($N7,$C$11:$H$152,4),IF($O7="n",F7))
S7:S11	S7	=IF(J7>I7,,F7)

 

[Marcílio_Lobão]

*michyb, Good morning.

Maybe I am missing something.

The lack of the argument in the IF function is not causing you problems?

O7:O11	O7	=IF($I7=$B$7, ,IF($J7<$B3,"n","y"))
P7:P11	P7	=IF($O7="n", ,VLOOKUP($N7,$C$11:$H$152,4))
Q7:Q11	Q7	=IF($O7="y",VLOOKUP($N7,$C$11:$H$152,4),IF($O7="n",F7 , ,))
S7:S11	S7	=IF(J7>I7, ,F7)

I hope it heps.

 

[Sufiyan97]

Is it what you want?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		October 2020					Sales need to be closed within the month to be paid commission in that month, else they are paid in month finalised
3
4		Date	Invoice No.	Customer PO No.	Customer Name	Amount	Amount Due	Status	Month end	Date Closed (date paid)	Salesperson		salesperson	invoice no	commiss	paid	not paid
5
6		3/10/2020	1148567	2146566/VO472895		2834	0	Closed	44136	11/12/2020				1148567	n		2834
7		5/10/2020	1148611	2146539		2557.5	0	Closed	44136	11/12/2020				1148611	y	2557.5
8		27/10/2020	1148683			295	-5	Credit	44136					1148683	n		295
9		26/10/2020	1148690	2137991		6882.7	0	Closed	44136	11/12/2020				1148690	y	6882.7
10		26/10/2020	1148728	2161436		7810	0	Closed	44136	11/12/2020				1148728	y	7810
11
12
13
14
15
16
17
Sheet1

Cell Formulas
Range	Formula
N6:N10	N6	=C6
O6:O10	O6	=IF($I6=$B$6,"",IF($J6<$B2,"n","y"))
P6:P10	P6	=IF($O6="n","",VLOOKUP($N6,$C$6:$H$148,4))
Q6:Q10	Q6	=IF($O6="y","",VLOOKUP($N6,$C$6:$H$148,4))

 

[michyb]

ooh I will try that on my sheet and let you know

 

[michyb]

Is it what you want?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		October 2020					Sales need to be closed within the month to be paid commission in that month, else they are paid in month finalised
3
4		Date	Invoice No.	Customer PO No.	Customer Name	Amount	Amount Due	Status	Month end	Date Closed (date paid)	Salesperson		salesperson	invoice no	commiss	paid	not paid
5
6		3/10/2020	1148567	2146566/VO472895		2834	0	Closed	44136	11/12/2020				1148567	n		2834
7		5/10/2020	1148611	2146539		2557.5	0	Closed	44136	11/12/2020				1148611	y	2557.5
8		27/10/2020	1148683			295	-5	Credit	44136					1148683	n		295
9		26/10/2020	1148690	2137991		6882.7	0	Closed	44136	11/12/2020				1148690	y	6882.7
10		26/10/2020	1148728	2161436		7810	0	Closed	44136	11/12/2020				1148728	y	7810
11
12
13
14
15
16
17
Sheet1

Cell Formulas
Range	Formula
N6:N10	N6	=C6
O6:O10	O6	=IF($I6=$B$6,"",IF($J6<$B2,"n","y"))
P6:P10	P6	=IF($O6="n","",VLOOKUP($N6,$C$6:$H$148,4))
Q6:Q10	Q6	=IF($O6="y","",VLOOKUP($N6,$C$6:$H$148,4))

this does work to a point :D
however, and this was my stuff up, if i have a date within the month its not picked up for some reason. can i modify the formula to > 1/10 and < 31/10? Im out of practice with formulas i dont use them enough
Thankyou for your help in advance!

 

[Sufiyan97]

this does work to a point :D
however, and this was my stuff up, if i have a date within the month its not picked up for some reason. can i modify the formula to > 1/10 and < 31/10? Im out of practice with formulas i dont use them enough
Thankyou for your help in advance!

Dates in which column?