Sum values in column O until empty cell is reached whilst excluding "NO" values

[jfin1ty]

Hi all,

Hoping someone can help me with this query.
I have a workbook I am working on that currently sums all the values in column "O" if the value on the same row in column "L" is "WP" or "BS" until an empty cell is reached.
I would like to expand upon this formula so that it still sums like I have mentioned above but will not include summing for any value in column "O" if the value on the same row in column AC is equal to NO. Is this possible?
I have made the start of the formula have some if, or, and, offset evaluations that are required for a plethora of reasons I'd rather not get into so really I'd imagine it would just be a tweak to the SUM formula part onwards?

Book1
	A	B	C	D	E		G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1															T
2				RG			11.559	SP000000				WP			2.705
3												WP			0.960
4												WP			1.945
5												WP			0.634
6												WP			0.480
7												WP			2.555
8								SP000001				BS			1.220
9												BS			1.060
10
11				RG			12.154	SP000002				WP			0.955
12												WP			0.556
13												WP			2.128
14												WP			5.720
15												WP			2.795
Sheet1

Cell Formulas
Range	Formula
G2:G15	G2	=IF(OR(H2<>"",D2<>""),IF(AND(O2<>"",OR(OFFSET(O2,-1,0)="",OFFSET(O2,-1,0)="T")),SUM(O2:INDEX(O2:$O$101,MATCH(TRUE,($O2:O$101=""),0))),""),"")

 

[bebo021999]

Book1
	G	H	I	J	K	L	M	N	O	P	Q												AC
2	10.925	SP000000				WP			2.705
3						WP			0.96
4						WP			1.945
5						WP			0.634														NO
6						WP			0.48
7						WP			2.555
8		SP000001				BS			1.22
9						BS			1.06
10
11	11.199	SP000002				WP			0.955														NO
12						WP			0.556
13						WP			2.128
14						WP			5.72
15						WP			2.795
Sheet1

Cell Formulas
Range	Formula
G2:G14	G2	=IF(OR(O1="",O1="T"),SUMPRODUCT(SUMIFS($O2:$O$15,$AC2:$AC$15,"<>NO",$L2:$L$15,{"WP","BS"}))-SUM($G3:$G$15),"")

 

[Peter_SSs]

I'm not quite sure how column H fits into it or the "T" in O1, but in any case I would be trying to avoid the volatile function OFFSET if possible.
Could we employ something like we did in another similar thread of yourd recently?

23 05 23.xlsm
	D			G	H				L			O														AC
1												T
2	RG			6.32	SP000000				WP			2.705
3									WP			0.96														NO
4									WP			1.945														NO
5									WP			0.634														NO
6									WP			0.48														NO
7									WP			2.555
8					SP000001				BS			1.22														NO
9									BS			1.06
10
11	RG			7.848	SP000002				WP			0.955														NO
12									WP			0.556														NO
13									WP			2.128
14									WP			5.72
15									WP			2.795														NO
16
Sum Ranges (2)

Cell Formulas
Range	Formula
G2:G16	G2	=IF(D2="","",SUM(SUMIFS(O2:O$100,L2:L$100,{"WP","BS"},AC2:AC$100,"<>NO"))-SUM(G3:G$100))

 

[jfin1ty]

I'm not quite sure how column H fits into it or the "T" in O1, but in any case I would be trying to avoid the volatile function OFFSET if possible.
Could we employ something like we did in another similar thread of yourd recently?

23 05 23.xlsm
	D			G	H				L			O														AC
1												T
2	RG			6.32	SP000000				WP			2.705
3									WP			0.96														NO
4									WP			1.945														NO
5									WP			0.634														NO
6									WP			0.48														NO
7									WP			2.555
8					SP000001				BS			1.22														NO
9									BS			1.06
10
11	RG			7.848	SP000002				WP			0.955														NO
12									WP			0.556														NO
13									WP			2.128
14									WP			5.72
15									WP			2.795														NO
16
Sum Ranges (2)

Cell Formulas
Range	Formula
G2:G16	G2	=IF(D2="","",SUM(SUMIFS(O2:O$100,L2:L$100,{"WP","BS"},AC2:AC$100,"<>NO"))-SUM(G3:G$100))

Hi Peter,
I was using the offset function because when someone inserts rows into the spreadsheet it changes the "-SUM(G5:G$100)" function and no longer calculates correctly.
I think the volatile offset function will be okay to use as the workbook I'm creating is actually a weekly template and each sheet on it only gets used for the day.
It really works quite well for what I need it to do. I was hoping to just expand on the existing formula to include the part about not summing any values in column O that have the value No in column AC.
I have to be a bit careful with what information I share as some of it is sensitive but I have tried to pull together a test copy of the sheet so you can better see what I'm working with.

I have cleared out a lot of the column headers and only left the important ones but basically it's for organizing and tracking loads on trucks for a day.
Column AC is changed to no if a delivery is missed on a shipment. Each block of rows until a blank row represents an entire load.
The "from" column represents the location something is loaded from but the amount from the different location needs to still be included in the load (t) total.
I've added the table that the vlookup sources the rego, type and truck (t) from in its most basic form too underneath the main table.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2
3	Run #	Name	Rego	TYPE	Truck (t)	Plus/Minus	Load (t)	Shipment	Column1	Column2	Column3	FROM	Column4	Column5	Tonnes (t)	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18	Delv'd / RT'D
4	1	TEST	REG1	RG	12.000		11.559	1	N/A	N/A	N/A	WP			2.705
5									N/A	N/A	N/A	WP			0.960
6									N/A	N/A	N/A	WP			1.945
7									N/A	N/A	N/A	WP			0.634
8									N/A	N/A	N/A	WP			0.480
9									N/A	N/A	N/A	WP			2.555
10								2	N/A	N/A	N/A	BS			1.220
11									N/A	N/A	N/A	BS			1.060
12
13	1	TEST	REG2	RG	12.500		12.154	3	N/A	N/A	N/A	WP			0.955
14									N/A	N/A	N/A	WP			0.556
15									N/A	N/A	N/A	WP			2.128
16									N/A	N/A	N/A	WP			5.720
17									N/A	N/A	N/A	WP			2.795
18
19	2	TEST	REG2	RG	12.500		11.570	4	N/A	N/A	N/A	WP			11.570
20
21	1	TEST	REG3	RG	16.000		15.000	5	N/A	N/A	N/A	WP			15.000
22
23	2	TEST	REG3	RG	16.000		15.450	6	N/A	N/A	N/A	WP			6.782
24									N/A	N/A	N/A	WP			2.288
25									N/A	N/A	N/A	WP			6.380
26
27	3	TEST	REG2	RG	12.500		10.000	7	N/A	N/A	N/A	BS			10.000
28
29		REGO	TYPE	Truck (t)
30		REG1	RG	12
31		REG2	RG	12.5
32		REG3	RG	16
Sheet1

Cell Formulas
Range	Formula
D4:D27	D4	=IFERROR(VLOOKUP(C4,Table1[#All],2,FALSE),"")
E4:E27	E4	=IFERROR(VLOOKUP(C4,Table1[#All],3,FALSE)+F4,"")
G4:G27	G4	=IF(OR(H4<>"",D4<>""),IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),SUM(O4:INDEX(O4:$O$100,MATCH(TRUE,($O4:O$100=""),0))),""),"")

Named Ranges
Name	Refers To	Cells
rego	=Table1[REGO]	D4:E27
type	=Table1[TYPE]	D4:E27

 

[Peter_SSs]

I think the volatile offset function will be okay to use ..

I would still be looking for an alternative (& I'm almost certain there will be one) but to answeer your question, what about this?

=LET(r,MATCH(TRUE,($O4:O$101=""),0),IF(OR(H4<>"",D4<>""),IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),SUMIF(AC4:INDEX(AC4:$AC$101,r),"<>NO",O4:INDEX(O4:$O$101,r)),""),""))

I note that this and your formula in post #4 now are taking no account of column L needing to be WP or BS. Has that requirement disappeared? Or going by your latest sample data, they are the only non-blank values in column L?

If you did want to investigate a non-volatile alternative, I would want to know (in words) just what this part of your formula is trying to do
IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),
In your sample data it seems that for every row that has a value in column D, the above conditions are met and for the only row where column H has a value and column D does not have a value the above conditions are not met.
Based on the given sample then, it would seem that we should only need to check if column D has a value to determine if a value is required in column G.

 

[jfin1ty]

I would still be looking for an alternative (& I'm almost certain there will be one) but to answeer your question, what about this?

=LET(r,MATCH(TRUE,($O4:O$101=""),0),IF(OR(H4<>"",D4<>""),IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),SUMIF(AC4:INDEX(AC4:$AC$101,r),"<>NO",O4:INDEX(O4:$O$101,r)),""),""))

I note that this and your formula in post #4 now are taking no account of column L needing to be WP or BS. Has that requirement disappeared? Or going by your latest sample data, they are the only non-blank values in column L?

If you did want to investigate a non-volatile alternative, I would want to know (in words) just what this part of your formula is trying to do
IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),
In your sample data it seems that for every row that has a value in column D, the above conditions are met and for the only row where column H has a value and column D does not have a value the above conditions are not met.
Based on the given sample then, it would seem that we should only need to check if column D has a value to determine if a value is required in column G.

Hi Peter,
Yes, sorry! The requirement for only summing values that are "WP" and "BS" are still there for column L. I must have accidentally removed it in my attempt to make a testing sheet.

As for the logic behind needing the formula not to sum when H is blank, this is because until a shipment has a shipment number against it, the sum of the load is not finalized. Basically, nothing should sum unless a rego is assigned to the load (column d) or a shipment number is assigned to the load (column H).
Can both things be added back into your formula? Your new formula is great by the way. Way beyond me.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2
3	Run #	Name	Rego	TYPE	Truck (t)	Plus/Minus	Load (t)	Shipment	Column1	Column2	Column3	FROM	Column4	Column5	Tonnes (t)	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18	Delv'd / RT'D
4	1	TEST	REG1	RG	12.000		9.004	1	N/A	N/A	N/A	WP			2.705
5									N/A	N/A	N/A	WP			0.960
6									N/A	N/A	N/A	WP			1.945
7									N/A	N/A	N/A	WP			0.634
8									N/A	N/A	N/A	WP			0.480
9									N/A	N/A	N/A	WP			2.555														No
10								2	N/A	N/A	N/A	BS			1.220
11									N/A	N/A	N/A	BS			1.060
12
13	1	TEST	REG2	RG	12.500		12.154	3	N/A	N/A	N/A	WP			0.955
14									N/A	N/A	N/A	WP			0.556
15									N/A	N/A	N/A	WP			2.128
16									N/A	N/A	N/A	WP			5.720
17									N/A	N/A	N/A	WP			2.795
18
19	2	TEST	REG2	RG	12.500		11.570	4	N/A	N/A	N/A	WP			11.570
20
21	1	TEST	REG3	RG	16.000		15.000	5	N/A	N/A	N/A	WP			15.000
22
23	2	TEST	REG3	RG	16.000		15.450	6	N/A	N/A	N/A	WP			6.782
24									N/A	N/A	N/A	WP			2.288
25									N/A	N/A	N/A	WP			6.380
26
27	3	TEST	REG2	RG	12.500		10.000	7	N/A	N/A	N/A	BS			10.000
Sheet1

Cell Formulas
Range	Formula
D4:D27	D4	=IFERROR(VLOOKUP(C4,Table1[#All],2,FALSE),"")
E4:E27	E4	=IFERROR(VLOOKUP(C4,Table1[#All],3,FALSE)+F4,"")
G4:G27	G4	=LET(r,MATCH(TRUE,($O4:O$96=""),0),IF(OR(H4<>"",D4<>""),IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),SUMIF(AC4:INDEX(AC4:$AC$96,r),"<>NO",O4:INDEX(O4:$O$96,r)),""),""))

Named Ranges
Name	Refers To	Cells
rego	=Table1[REGO]	D4:E27
type	=Table1[TYPE]	D4:E27

The reason I have "IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)"))," in the formula because when new rows are inserted into the sheet, it was changing the "-SUM(G3:G$100))" in your original formula. The users of the sheet will frequently insert new rows to move/ split up shipments to assign them to different trucks etc. For example, if 5 rows are added below row 5 this makes the formula in G5 change from "-SUM(G6:G$100))" to be "-SUM(G11:G$100))". It will then revert back on row 6 to "-SUM(G7:G$100))" etc. See below. It could just be me being a noob.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
3	Run #	Name	Rego	TYPE	Truck (t)	Plus/Minus	Load (t)	Shipment	Column1	Column2	Column3	FROM	Column4	Column5	Tonnes (t)	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18	Delv'd / RT'D
4	1	TEST	REG1	RG	12.000		11.559	1	N/A	N/A	N/A	WP			2.705
5									N/A	N/A	N/A	WP			0.960
6
7
8
9
10
11									N/A	N/A	N/A	WP			1.945
12									N/A	N/A	N/A	WP			0.634
13									N/A	N/A	N/A	WP			0.480
14									N/A	N/A	N/A	WP			2.555
15								2	N/A	N/A	N/A	BS			1.220
16									N/A	N/A	N/A	BS			1.060
17
18	1	TEST	REG2	RG	12.500		12.154	3	N/A	N/A	N/A	WP			0.955
19									N/A	N/A	N/A	WP			0.556
20									N/A	N/A	N/A	WP			2.128
21									N/A	N/A	N/A	WP			5.720
22									N/A	N/A	N/A	WP			2.795
23
24	2	TEST	REG2	RG	12.500		11.570	4	N/A	N/A	N/A	WP			11.570
25
26	1	TEST	REG3	RG	16.000		15.000	5	N/A	N/A	N/A	WP			15.000
27
28	2	TEST	REG3	RG	16.000		15.450	6	N/A	N/A	N/A	WP			6.782
29									N/A	N/A	N/A	WP			2.288
30									N/A	N/A	N/A	WP			6.380
31
32	3	TEST	REG2	RG	12.500		10.000	7	N/A	N/A	N/A	BS			10.000
Sheet1

Cell Formulas
Range	Formula
D4:D32	D4	=IFERROR(VLOOKUP(C4,Table1[#All],2,FALSE),"")
E4:E32	E4	=IFERROR(VLOOKUP(C4,Table1[#All],3,FALSE)+F4,"")
G4,G6:G32	G4	=IF(D4="","",SUM(SUMIFS(O4:O$105,L4:L$105,{"WP","BS"},AC4:AC$105,"<>NO"))-SUM(G5:G$105))
G5	G5	=IF(D5="","",SUM(SUMIFS(O5:O$105,L5:L$105,{"WP","BS"},AC5:AC$105,"<>NO"))-SUM(G11:G$105))

Named Ranges
Name	Refers To	Cells
rego	=Table1[REGO]	D4:E32
type	=Table1[TYPE]	D4:E32

 

[Peter_SSs]

nothing should sum unless a rego is assigned to the load (column d) or a shipment number is assigned to the load (column H).

So why is there no value in cell G10 of post #4 since there is a shipment number in column H?

 

[jfin1ty]

So why is there no value in cell G10 of post #4 since there is a shipment number in column H?

Hi Peter,
There is no value in cell G10 due to the neither of the offset criteria being fulfilled. If row 9 cell O9 is not blank or equal to "Tonnes (t)" then the blank value is returned to G10.
In this formula however it does not account for the need to count only "WP" and "BS". It just sums everything until a blank cell is reached.
Your new formula is far better. Is there a way to amend it to include summing only for column O values where rows contain WP and BS as well as nothing summing unless a rego is assigned to the load (column d) or a shipment number is assigned to the load (column H)?

=LET(r,MATCH(TRUE,($O4:O$101=""),0),IF(OR(H4<>"",D4<>""),IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),SUMIF(AC4:INDEX(AC4:$AC$101,r),"<>NO",O4:INDEX(O4:$O$101,r)),""),""))

 

[jfin1ty]

Hi Peter,
There is no value in cell G10 due to the neither of the offset criteria being fulfilled. If row 9 cell O9 is not blank or equal to "Tonnes (t)" then the blank value is returned to G10.
In this formula however it does not account for the need to count only "WP" and "BS". It just sums everything until a blank cell is reached.
Your new formula is far better. Is there a way to amend it to include summing only for column O values where rows contain WP and BS as well as nothing summing unless a rego is assigned to the load (column d) or a shipment number is assigned to the load (column H)?

[CODE=xls]=LET(r,MATCH(TRUE,($O4:O$101=""),0),IF(OR(H4<>"",D4<>""),IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),SUMIF(AC4:INDEX(AC4:$AC$101,r),"<>NO",O4:INDEX(O4:$O$101,r)),""),""))

[/CODE]

Hi Peter,

Would something like this work?

=LET(r,MATCH(TRUE,($O4:O$101=""),0),IF(OR(H4<>"",D4<>""),IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),(SUMIF(AC4:INDEX(AC4:$AC$101,r),"<>NO",O4:INDEX(O4:$O$101,r))-SUMIF(L4:INDEX(L4:$L$101,r),"<>WP",O4:INDEX(O4:$O$101,r))-SUMIF(L4:INDEX(L4:$L$101,r),"<>BS",O4:INDEX(O4:$O$101,r))),""),""))

 

[jfin1ty]

Hi Peter,

Would something like this work?

=LET(r,MATCH(TRUE,($O4:O$101=""),0),IF(OR(H4<>"",D4<>""),IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),(SUMIF(AC4:INDEX(AC4:$AC$101,r),"<>NO",O4:INDEX(O4:$O$101,r))-SUMIF(L4:INDEX(L4:$L$101,r),"<>WP",O4:INDEX(O4:$O$101,r))-SUMIF(L4:INDEX(L4:$L$101,r),"<>BS",O4:INDEX(O4:$O$101,r))),""),""))

Hi Peter,
It's long and complex and volitile but I think I worked it out.
The below will sum everything that is not NO in column AH along with everything that is from BS plus everything in column AH that is not NO along with everything that is from WP.
It's not affected by anyone inserting rows thanks to the offset and it wont sum anything at all if either the shipment or the type is blank.

=LET(r,MATCH(TRUE,($O4:O$101=""),0),IF(OR(H4<>"",D4<>""),IF(AND(O4<>"",OR(OFFSET(O4,-1,0)="",OFFSET(O4,-1,0)="Tonnes (t)")),SUMIFS(O4:INDEX(O4:$O$101,r),AC4:INDEX(AC4:$AC$101,r),"<>NO",L4:INDEX(L4:$L$101,r),"BS")+SUMIFS(O4:INDEX(O4:$O$101,r),AC4:INDEX(AC4:$AC$101,r),"<>NO",L4:INDEX(L4:$L$101,r),"WP"),""),""))