Have I overcomplicated this formula?

[Wishmaster89]

Hi Everyone

I've written a formula in column AC that looks up the id number in column D on the "operations team orders" sheet and then checks to see if the shipment dates month ( column I) matches the order date month on the "operations team orders" sheet. In most cases orders are shipped within 5 days of ordering so that's why in the formula it is "ordDate+5".

What i am wondering is have i over complicated this formula and secondly i was worried that the sumif was going to ignore the if statements i have applied even though the sum if only happens if those conditions are met.

Is there a better way to do this formula?

Note: ignore column H on the first sheet, despite being named 'order date' its irrelavent.

CI invoices stuff.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W						AC
1	Posting Date	Document No.	Line No.	Sell-to Customer No.	No.	Order No.	Customer Reference No	Order Date	Shipment Date	Description	Quantity	Unit Price	VAT %	Amount	VAT	Amount Including VAT	Bill-to Customer No.	Sell-to Customer Name	Forename	Surname	Order total	Orders sheet total	Difference						Amount from orders sheet
2	30/06/2023	test	1880000	001770110H	LM3_QUESTION_PACK			30/06/2023	06/06/2023	test	1	29.17	20	£21.60	£4.32	£25.92	COLQQL	test	test name	test name	222.3	231.8	9.5						£231.80
3	30/06/2023	test	1930000	001775028D	M80_EKEY_FACTS			30/06/2023	06/06/2023	test	1	30	0	£27.00	£0.00	£27.00	COLQQL	test	test name	test name	438.3	411.3	-27						£411.30
4	30/06/2023	test	2060000	001819431L	M85_EKEY_FACTS			30/06/2023	06/06/2023	test	1	30	0	£27.00	£0.00	£27.00	COLQQL	test	test name	test name	438.3	411.3	-27						£411.30
5	30/06/2023	test	2150000	001835987F	13420			30/06/2023	16/06/2023	test	1	8	0	£8.00	£0.00	£8.00	COLQQL	test	test name	test name	250.1	251.7	1.6						£259.70
CI export

Cell Formulas
Range	Formula
V2:V5	V2	=SUMIF('[Copy of COLQQL 06072023 JULY.xlsx]Orders'!$D$5:$D$2211,D2,'[Copy of COLQQL 06072023 JULY.xlsx]Orders'!$K$5:$K$2211)
W2:W5	W2	=V2-U2
AC2:AC5	AC2	=LET(ordDate,XLOOKUP(D2&MONTH(I2),'Operations team orders'!$D$2:$D$5&MONTH('Operations team orders'!$A$2:$A$5),'Operations team orders'!$A$2:$A$5),IF(OR(ordDate=I2,I2<=ordDate+5),SUMIF('Operations team orders'!$D$1:$D$5,'CI export'!D2,'Operations team orders'!$I$1:$I$5),"not in range"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
W2:W5	Cell Value	<>0	text	NO
W2:W5	Cell Value	=0	text	NO

CI invoices stuff.xlsx
	A	B	C	D	E	F	G	H	I
1	Date of order	Learner first name	Learner surname	CII PIN		CII member/Non-member	price	postage	price with vat
2	01/06/2023	test	test	001770110H	test	member	231.8	0	231.8
3	01/06/2023	test	test	001775028D	test	member	411.3	0	411.3
4	01/06/2023	test	test	001819431L	test	member	411.3	0	411.3
5	12/06/2023	test	test	001835987F	test	member	251.7	8	259.7
Operations team orders

 

[awoohaw]

Hi, you have references to another workbook in your formulas:
'[Copy of COLQQL 06072023 JULY.xlsx] What kind of data is this?

 

[Wishmaster89]

Hi, you have references to another workbook in your formulas:
'[Copy of COLQQL 06072023 JULY.xlsx] What kind of data is this?

Hi , thanks for pointing that out. Please ignore it, its only the formulas in column AC i'm asking about

 

[awoohaw]

Hi, after I looked at it some more I have more questions.
You talk about just comparing MONTHS/5 Days from a Date, but you don't have a BOOLEAN exprssion in AC it calculates something. What does that do.
I've consolidated your two sheets into one worksheet:

Mr excel questions 53.xlsm
	A	B	C	D	E	F	G	H	I
1	Date of order	Learner first name	Learner surname	CII PIN		CII member/Non-member	price	postage	price with vat
2	2023-06-01	test	test	001770110H	test	member	231.8	0	231.8
3	2023-06-01	test	test	001775028D	test	member	411.3	0	411.3
4	2023-06-01	test	test	001819431L	test	member	411.3	0	411.3
5	2023-06-12	test	test	001835987F	test	member	251.7	8	259.7
6
7	D	I	AC
8	Sell-to Customer No.	Shipment Date	Amount from orders sheet
9	001770110H	2023-06-06	FALSE	=(XLOOKUP($A9,$D$2:$D$5,MONTH($A$2:$A$5))+5)>=B9
10	001775028D	2023-06-06	FALSE	=(XLOOKUP($A10,$D$2:$D$5,MONTH($A$2:$A$5))+5)>=B10
11	001819431L	2023-06-06	FALSE	=(XLOOKUP($A11,$D$2:$D$5,MONTH($A$2:$A$5))+5)>=B11
12	001835987F	2023-06-16	FALSE	=(XLOOKUP($A12,$D$2:$D$5,MONTH($A$2:$A$5))+5)>=B12
Wishmaster89

Cell Formulas
Range	Formula
C9:C12	C9	=(XLOOKUP($A9,$D$2:$D$5,MONTH($A$2:$A$5))+5)>=B9
D9:D12	D9	=FORMULATEXT(C9)

 

[Wishmaster89]

Hi, after I looked at it some more I have more questions.
You talk about just comparing MONTHS/5 Days from a Date, but you don't have a BOOLEAN exprssion in AC it calculates something. What does that do.
I've consolidated your two sheets into one worksheet:

Mr excel questions 53.xlsm
	A	B	C	D	E	F	G	H	I
1	Date of order	Learner first name	Learner surname	CII PIN		CII member/Non-member	price	postage	price with vat
2	2023-06-01	test	test	001770110H	test	member	231.8	0	231.8
3	2023-06-01	test	test	001775028D	test	member	411.3	0	411.3
4	2023-06-01	test	test	001819431L	test	member	411.3	0	411.3
5	2023-06-12	test	test	001835987F	test	member	251.7	8	259.7
6
7	D	I	AC
8	Sell-to Customer No.	Shipment Date	Amount from orders sheet
9	001770110H	2023-06-06	FALSE	=(XLOOKUP($A9,$D$2:$D$5,MONTH($A$2:$A$5))+5)>=B9
10	001775028D	2023-06-06	FALSE	=(XLOOKUP($A10,$D$2:$D$5,MONTH($A$2:$A$5))+5)>=B10
11	001819431L	2023-06-06	FALSE	=(XLOOKUP($A11,$D$2:$D$5,MONTH($A$2:$A$5))+5)>=B11
12	001835987F	2023-06-16	FALSE	=(XLOOKUP($A12,$D$2:$D$5,MONTH($A$2:$A$5))+5)>=B12
Wishmaster89

Cell Formulas
Range	Formula
C9:C12	C9	=(XLOOKUP($A9,$D$2:$D$5,MONTH($A$2:$A$5))+5)>=B9
D9:D12	D9	=FORMULATEXT(C9)

Thanks for taking the time to look some more. So the formula is looking up the id number and order date from the orders sheet. If the ID and month both match then it will add 5 days to that order date and if the shipping date is equal to or less than the order date it will do a sumif on the orders sheet column I by CII Pin number ( column D )

The reason for the +5 days is because the shipping date is usually no more than 5 days from the order date but its never on the same day so you couldn't do "if order date equals shipping date"

Hope this helps

 

[awoohaw]

Thanks for taking the time to look some more. So the formula is looking up the id number and order date from the orders sheet. If the ID and month both match then it will add 5 days to that order date and if the shipping date is equal to or less than the order date it will do a sumif on the orders sheet column I by CII Pin number ( column D )

The reason for the +5 days is because the shipping date is usually no more than 5 days from the order date but its never on the same day so you couldn't do "if order date equals shipping date"

Hope this helps

How does this work:
Depending on which version of excel you use, you may need to use the SUMPRODUCT function instead of SUM, and you may need to 'commit" the formula as an array formula by using the CNTL-SHFT-ENTR keystroke combinatio. There are examples of all three methods below.

Mr excel questions 53.xlsm
	A	B	C	D	E	F	G	H	I
1	Date of order	Learner first name	Learner surname	CII PIN		CII member/Non-member	price	postage	price with vat
2	2023-06-01	test	test	001770110H	test	member	231.8	0	231.8
3	2023-06-01	test	test	001775028D	test	member	500	0	550
4	2023-06-01	test	test	001775028D	test	member	500	0	550
5	2023-05-01	test	test	001819431L	test	member	411.3	0	411.3
6	2023-06-12	test	test	001835987F	test	member	251.7	8	259.7
7
8	D	I	AC
9	Sell-to Customer No.	Shipment Date	Amount from orders sheet
10	001770110H	2023-06-06	231.8	= IF(SUM(($A$2:$A$6+5>=B10) * ($D$2:$D$6=A10))< 1,"Not In Range", SUM(($A$2:$A$6+5>=B10) * ($D$2:$D$6=A10) * ($I$2:$I$6)))
11	001775028D	2023-06-06	1100	{= IF(SUM(($A$2:$A$6+5>=B11) * ($D$2:$D$6=A11))< 1,"Not In Range", SUM(($A$2:$A$6+5>=B11) * ($D$2:$D$6=A11) * ($I$2:$I$6)))}
12	001819431L	2023-06-06	Not In Range	= IF(SUMPRODUCT(($A$2:$A$6+5>=B12) * ($D$2:$D$6=A12))< 1,"Not In Range", SUMPRODUCT(($A$2:$A$6+5>=B12) * ($D$2:$D$6=A12) * ($I$2:$I$6)))
13	001835987F	2023-06-16	259.7	= IF(SUMPRODUCT(($A$2:$A$6+5>=B13) * ($D$2:$D$6=A13))< 1,"Not In Range", SUMPRODUCT(($A$2:$A$6+5>=B13) * ($D$2:$D$6=A13) * ($I$2:$I$6)))
Wishmaster89

Cell Formulas
Range	Formula
C10	C10	= IF(SUM(($A$2:$A$6+5>=B10) * ($D$2:$D$6=A10))< 1,"Not In Range", SUM(($A$2:$A$6+5>=B10) * ($D$2:$D$6=A10) * ($I$2:$I$6)))
D10:D13	D10	=FORMULATEXT(C10)
C11	C11	= IF(SUM(($A$2:$A$6+5>=B11) * ($D$2:$D$6=A11))< 1,"Not In Range", SUM(($A$2:$A$6+5>=B11) * ($D$2:$D$6=A11) * ($I$2:$I$6)))
C12:C13	C12	= IF(SUMPRODUCT(($A$2:$A$6+5>=B12) * ($D$2:$D$6=A12))< 1,"Not In Range", SUMPRODUCT(($A$2:$A$6+5>=B12) * ($D$2:$D$6=A12) * ($I$2:$I$6)))
Press CTRL+SHIFT+ENTER to enter array formulas.