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]

 

[jarett]

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)))))

As I mentioned to a previous post, my data is not true, I refreshed the spreadsheet today and it does not have any blank cells, there is a value in each one. The lines with one record are calculating correctly. Records with multiple records are off, it loooks like they are summing all values in M, N, and subtracting all values from the sum of O. I just need the first value because they are repeating, or should I figure out how to remove those "duplicate" values before it even gets to the formula? I tried both versions of your LET formula @Peter_SSs and neither output the correct values when I corrected the data. This sample data set is fresh and the value in column S is what P2 should equal. P14 is the only cell that calculated correctly.

AR_Bartlett_COPY.xlsx
	A											L	M	N	O	P	Q	R	S
1	InvoiceNo											ExtensionAmt	FreightAmt	SalesTaxAmt	DepositAmt	Net Invoice			231.63
2	0056404											35	8.86	18.77	0	286.89
3	0056404											78	8.86	18.77	0
4	0056404											91	8.86	18.77	0
5	0056406											261	15.48	0	2.48	416
6	0056406											79	15.48	0	2.48
7	0056406											37	15.48	0	2.48
8	0056408											79	8.73	14.45	0	203.36
9	0056408											78	8.73	14.45	0
10	0056409											75	11	15.02	0	203.04
11	0056409											76	11	15.02	0
12	0056410											39	10.34	0	0	159.68
13	0056410											100	10.34	0	0
14	0056411											42	8.25	4.15	0	54.4
15	0056412											0	13.35	23.63	15.87	357.44
16	0056412											154	13.35	23.63	15.87
17	0056412											77	13.35	23.63	15.87
18	0056412											42	13.35	23.63	15.87
AR_Bartlett

Cell Formulas
Range	Formula
P2:P18	P2	=IF(A2=A1,"",SUMIF(A:A,A2,L:L)+SUMIF(A:A,A2,M:M)+SUMIF(A:A,A2,N:N)-SUMIF(A:A,A2,O:O))

Named Ranges
Name	Refers To	Cells
ExternalData_1	=AR_Bartlett!$A$1:$O$290	P2:P18

 

[Asbestos_Jen]

Try this modification:

=IF(A2=A1,"",SUMIF(A:A,A2,L:L)+(M2+N2)-O2)

 

[jarett]

Try this modification:

=IF(A2=A1,"",SUMIF(A:A,A2,L:L)+(M2+N2)-O2)

Inputted into data and everything looks good so far, will check it again when we have to refresh in another week or so.

 

[Peter_SSs]

I tried both versions of your LET formula @Peter_SSs and neither output the correct values when I corrected the data.

No, that's because your data structure & requirement is quite different to what you had posted earlier.
This amendment to my structured reference-style formula gives the same results as the SUMIF suggestion in post 12 for the sample data provided.

=LET(SO,[@InvoiceNo],d,FILTER(AR_Bartlett[[ExtensionAmt]:[DepositAmt]],[InvoiceNo]=SO),IF(SO=OFFSET(SO,-1,0),"",SUM(TAKE(d,,1),TAKE(d,1)*{0,1,1,-1})))

However, given the changed structure I would recommend the SUMIF approach over this as it is a more efficient calculation (as well as shorter formula of course. )

A very minor point but the SUMIF suggestion does have one set of parentheses that are not needed.

=IF(A2=A1,"",SUMIF(A:A,A2,L:L)+M2+N2-O2)