Show Total Only Once for Order# with Multiple Lines

[jarett]

I have this spreadsheet that contains order lines, some orders only have one line, easy enough to display to "net invoice" total. The orders with multiple lines is where I am stuck. If the order has multiple lines, the "additional charges/deductions" are always on the first line of the order. So if an order has multiple lines I need to add the (total of column L to column M+N)-O=P, and I only want to display P on the first line of the order. Column L is a detail field and M,N,O are all header fields in the DB I am pulling from.

INVOICE 03012024.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	SalesOrderNo	InvoiceDate	UDF_EMPLOYEE_NAME	UDF_EXTCUSTPO	UDF_EXTRAORDERINFORMATION	ItemCode	ItemCodeDesc	ShipToCity	ShipToState	ShipToZipCode	ExtensionAmt	FreightAmt	SalesTaxAmt	DepositAmt	Net Invoice
2	8432310	3/1/2024 0:00	AGUSTIN RODRIGUEZ	IKIHWYN7J	GARYVILLE	8051-33BK	High Sierra Elite Fly-By 17 in	Walker	LA	70785	78	11.53	7.76	0	97.29
3	8424191	3/1/2024 0:00	BOBBY RUMORE	WDHH8M0AH	SHELL NORCO	10022597-NAVY-MEDR	MNS FR AIR HENLEY LS TOP NAVY	Raceland	LA	70394	72	8.86	20.73	41.59	60
4	8424191	3/1/2024 0:00	BOBBY RUMORE	WDHH8M0AH	SHELL NORCO	10022599-GRAY-MEDR	MNS FR AIR HENLEY LS TOP SILVR	Raceland	LA	70394	72				72
5	8424191	3/1/2024 0:00	BOBBY RUMORE	WDHH8M0AH	SHELL NORCO	10022597-NAVY-MEDR	MNS FR AIR HENLEY LS TOP NAVY	Raceland	LA	70394	72				72
6	8425683	3/1/2024 0:00	BRANDON GONZALEZ	7Q6LCEJNB	GOLDEN PASS LNG	10023466-BLUE-2932	MNS FR M4 RLX STR DLT BSC BT C	Port Arthur	TX	77642	206	9.13	17.75	11.37	221.51
7	8425305	3/1/2024 0:00	GUILLERMO MENDOZA	2B6MZVIMT	CARSON	P-INDM10 42X30	42x30 10OZ FR MODERN JEANS	LONG BEACH	CA	90805	59	28.14	0	0	87.14
8	8425305	3/1/2024 0:00	GUILLERMO MENDOZA	2B6MZVIMT	CARSON	888830073032	Rambler 36oz Bottle Chug Navy	LONG BEACH	CA	90805	59		0	0	59
9	8425305	3/1/2024 0:00	GUILLERMO MENDOZA	2B6MZVIMT	CARSON	10023466-BLUE-4230	MNS FR M4 Relaxed Stretch Dura	LONG BEACH	CA	90805	103		0	0	103
10	8425305	3/1/2024 0:00	GUILLERMO MENDOZA	2B6MZVIMT	CARSON	10012258-GRAY-3XLTR	MNS FR Work Crew T-Shirt	LONG BEACH	CA	90805	83		0	0	83
11	8432443	3/1/2024 0:00	ANDREW GARCIA	5UASCACLJ	CITGO	306-396-XL	Tahoe	MISSION	TX	78574	40	11.6	4.26	0	55.86
12	8430820	3/1/2024 0:00	SHALTON LOPEZ-GARCIA	BMFBSK7OR	GBR	8051-33BK	High Sierra Elite Fly-By 17 in	NORTH BRUNSW	NJ	08902	78	9.85	0	3.21	84.64
AR_Bartlett

Cell Formulas
Range	Formula
P2:P12	P2	=SUM(AR_Bartlett[@[ExtensionAmt]:[SalesTaxAmt]])-[@DepositAmt]

 

[MaxSmart]

How about something like this.....

I could not get the L2BB function to work so had to just cut and paste;

OrderNo	Amount	Freight	Tax	Deposit	Total
12345​	500.00	12.00	25.00	130.00	407.00	=IF(A2=A1,"",SUMIF(A:A,A2,B:B)+SUMIF(A:A,A2,C:C)+SUMIF(A:A,A2,D:D)-SUMIF(A:A,A2,E:E))
78675​	230.00	17.00	12.00	200.00	221.00
78675​	120.00	11.00	5.00
78675​	25.00		1.00
98765​	300.00	17.00	27.00	75.00	269.00
99999​	100.00	5.00	7.00	50.00	62.00

 

[Peter_SSs]

Just wondering why you have asked in this forum not the 'Excel Questions' forum since it looks like an Excel question?

Assuming ..
- Excel,
- Keeping your formal Excel table nomenclature, and
- Using your 365 version,
.. see if this does what you want.

jarett.xlsm
	A	B										L	M	N	O	P
1		SalesOrderNo										ExtensionAmt	FreightAmt	SalesTaxAmt	DepositAmt	Net Invoice
2		8432310										78	11.53	7.76	0	97.29
3		8424191										72	8.86	20.73	41.59	204
4		8424191										72
5		8424191										72
6		8425683										206	9.13	17.75	11.37	221.51
7		8425305										59	28.14	0	0	332.14
8		8425305										59		0	0
9		8425305										103		0	0
10		8425305										83		0	0
11		8432443										40	11.6	4.26	0	55.86
12		8430820										78	9.85	0	3.21	84.64
13
14
Sheet1

Cell Formulas
Range	Formula
P2:P12	P2	=LET(SO,[@SalesOrderNo],d,FILTER(AR_Bartlett[[ExtensionAmt]:[DepositAmt]],[SalesOrderNo]=SO),IF(SO=OFFSET(SO,-1,0),"",SUM(TAKE(d,,3),-TAKE(d,,-1))))

 

[jarett]

Just wondering why you have asked in this forum not the 'Excel Questions' forum since it looks like an Excel question?

Assuming ..
- Excel,
- Keeping your formal Excel table nomenclature, and
- Using your 365 version,
.. see if this does what you want.

jarett.xlsm
	A	B										L	M	N	O	P
1		SalesOrderNo										ExtensionAmt	FreightAmt	SalesTaxAmt	DepositAmt	Net Invoice
2		8432310										78	11.53	7.76	0	97.29
3		8424191										72	8.86	20.73	41.59	204
4		8424191										72
5		8424191										72
6		8425683										206	9.13	17.75	11.37	221.51
7		8425305										59	28.14	0	0	332.14
8		8425305										59		0	0
9		8425305										103		0	0
10		8425305										83		0	0
11		8432443										40	11.6	4.26	0	55.86
12		8430820										78	9.85	0	3.21	84.64
13
14
Sheet1

Cell Formulas
Range	Formula
P2:P12	P2	=LET(SO,[@SalesOrderNo],d,FILTER(AR_Bartlett[[ExtensionAmt]:[DepositAmt]],[SalesOrderNo]=SO),IF(SO=OFFSET(SO,-1,0),"",SUM(TAKE(d,,3),-TAKE(d,,-1))))

That is my bad, it had been a while since I have posted something. I did try your solution and some lines were correct but others I received a #value error, all of the cells seem to have the correct format. I assume it might have something to do with the version type of excel I pasted the formula into?

 

[Peter_SSs]

OK, I've moved the thread to the Excel Questions forum.

I did try your solution and some lines were correct but others I received a #value error,

So could we have another small sample with XL2BB and ensure there are a few Sales Order numbers that work with my formula and a few that do not work with my formula? Before making the XL2BB Mini Sheet, hide the irrelevant columns like I did to keep the Mini Sheet smaller.

I received a #value error, .... I assume it might have something to do with the version type of excel I pasted the formula into?

If you pasted my formula into your 2016 version the formula should return a #NAME? error.
If it is working for some rows then I assume that you must be using it in MS365. Is that correct? (I did note in my previous post that I had assumed 365 version as the functions used are not all available in other versions)

 

[MaxSmart]

Did you try my version - it works for me

 

[Peter_SSs]

Did you try my version - it works for me

Given that it is marked as the solution it has most likely been tried.

 

[jarett]

OK, I've moved the thread to the Excel Questions forum.



So could we have another small sample with XL2BB and ensure there are a few Sales Order numbers that work with my formula and a few that do not work with my formula? Before making the XL2BB Mini Sheet, hide the irrelevant columns like I did to keep the Mini Sheet smaller.



If you pasted my formula into your 2016 version the formula should return a #NAME? error.
If it is working for some rows then I assume that you must be using it in MS365. Is that correct? (I did note in my previous post that I had assumed 365 version as the functions used are not all available in other versions)

The excel version is through our 365 license, Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20124) 64-bit

INVOICE 03012024.xlsx
	A	B										L	M	N	O	P
1	InvoiceNo	SalesOrderNo										ExtensionAmt	FreightAmt	SalesTaxAmt	DepositAmt	Net Invoice
2	0053687	8432310										78	11.53	7.76	0	97.29
3	0053689	8424191										72	8.86	20.73	41.59	204
4	0053689	8424191										72
5	0053689	8424191										72
6	0053690	8425683										206	9.13	17.75	11.37	221.51
7	0053691	8425305										59	28.14	0	0	332.14
8	0053691	8425305										59		0	0
9	0053691	8425305										103		0	0
10	0053691	8425305										83		0	0
11	0053693	8432443										40	11.6	4.26	0	55.86
12	0053695	8430820										78	9.85	0	3.21	84.64
13	0053696	8420299										68	21.43	0	2.43	#VALUE!
14	0053696	8420299										100		0
15	0053696	8420299										35		0
16	0053696	8420299										68		0
17	0053697	8425805										72	9.44	12.05	0	152.49
18	0053697	8425805										59			0
19	0053698	8433323										100	12.45	9.95	17.4	105
20	0053700	8432303										206	10.45	17.86	0	234.31
21	0053701	8427764										152	12.45	13.98	0	178.43
22	0053702	8429684										79	9.52	27.69	0	330.21
23	0053702	8429684										78			0
24	0053702	8429684										18			0
25	0053702	8429684										118			0
26	0053703	8427981										68	8.86	12.28	0	161.14
27	0053703	8427981										72			0
28	0053704	8407581										61	9.3	16.11	4.59	#VALUE!
29	0053704	8407581										68
30	0053704	8407581										37
AR_Bartlett

Cell Formulas
Range	Formula
P2:P30	P2	=LET(SO,[@SalesOrderNo],d,FILTER(AR_Bartlett[[ExtensionAmt]:[DepositAmt]],[SalesOrderNo]=SO),IF(SO=OFFSET(SO,-1,0),"",SUM(TAKE(d,,3),-TAKE(d,,-1))))

 

[Peter_SSs]

Thanks for the XL2BB sample. That has shown that the issue affecting my formula is that some of the "empty" cells in column O are not empty but contain space characters. Those cells are shown in yellow below. There are also space characters in the blue cells though they do not cause a problem in my formula.

To me, the ideal solution would be to clean up the original data so that such space characters are removed (yellow and blue) as that sort of thing often affects later results and is not obvious to look at.
Failing that, if you wanted to use a structured refence formula for the "Net Invoice" column, once the data is in this table you could either

• Find/Replace space characters in those columns, or at least column O, (with 'Match entire cell contents' set) and my previous formula should work
  or
• Leave those space characters alone and slightly change my formula as follows
  
  =LET(SO,[@SalesOrderNo],d,FILTER(AR_Bartlett[[ExtensionAmt]:[DepositAmt]],[SalesOrderNo]=SO),IF(SO=OFFSET(SO,-1,0),"",SUM(TAKE(d,,3),-N(TAKE(d,,-1)))))

Here it is with those space characters still in the yellow (& blue) cells.

jarett.xlsm
	B										L	M	N	O	P
1	SalesOrderNo										ExtensionAmt	FreightAmt	SalesTaxAmt	DepositAmt	Net Invoice
2	8432310										78	11.53	7.76	0	97.29
3	8424191										72	8.86	20.73	41.59	204
4	8424191										72
5	8424191										72
6	8425683										206	9.13	17.75	11.37	221.51
7	8425305										59	28.14	0	0	332.14
8	8425305										59		0	0
9	8425305										103		0	0
10	8425305										83		0	0
11	8432443										40	11.6	4.26	0	55.86
12	8430820										78	9.85	0	3.21	84.64
13	8420299										68	21.43	0	2.43	290
14	8420299										100		0
15	8420299										35		0
16	8420299										68		0
17	8425805										72	9.44	12.05	0	152.49
18	8425805										59			0
19	8433323										100	12.45	9.95	17.4	105
20	8432303										206	10.45	17.86	0	234.31
21	8427764										152	12.45	13.98	0	178.43
22	8429684										79	9.52	27.69	0	330.21
23	8429684										78			0
24	8429684										18			0
25	8429684										118			0
26	8427981										68	8.86	12.28	0	161.14
27	8427981										72			0
28	8407581										61	9.3	16.11	4.59	186.82
29	8407581										68
30	8407581										37
Sheet4

Cell Formulas
Range	Formula
P2:P30	P2	=LET(SO,[@SalesOrderNo],d,FILTER(AR_Bartlett[[ExtensionAmt]:[DepositAmt]],[SalesOrderNo]=SO),IF(SO=OFFSET(SO,-1,0),"",SUM(TAKE(d,,3),-N(TAKE(d,,-1)))))

 

[jarett]

How about something like this.....

I could not get the L2BB function to work so had to just cut and paste;

OrderNo	Amount	Freight	Tax	Deposit	Total
12345​	500.00	12.00	25.00	130.00	407.00	=IF(A2=A1,"",SUMIF(A:A,A2,B:B)+SUMIF(A:A,A2,C:C)+SUMIF(A:A,A2,D:D)-SUMIF(A:A,A2,E:E))
78675​	230.00	17.00	12.00	200.00	221.00
78675​	120.00	11.00	5.00
78675​	25.00		1.00
98765​	300.00	17.00	27.00	75.00	269.00
99999​	100.00	5.00	7.00	50.00	62.00

Notice some inconsistencies in your data compared to mine, and that is my fault, I must of pulled some data that had been edited after the refresh. This version is not working because C and D would only need to be summed once and E subtracted once, those columns have the same values if there are multiple lines. Going to reply to last post with new data.