Sum Values Above Based on Adjacent Value

[MikeCobra]

I am looking to see if this can be done as a formula but if it has to be VBA code then I can try and get it to work.

Basically, This file will track the Ins ( Column E) and Outs (Column F) of Totes. The outs will go out every day but the ins will only be delivered if the route is scheduled for a delivery that day. This can be seen in column I (Frequency). If there is the weekday in the Row (Column I). Then it will go out for delivery,

So if there is a day break between the delivery day (Column I - Frequency). The Wednesday IN (Column E) totes will be added to the Thursday in Totes as that is when they will be delivered.

So I would want the Formula to add all the values in Column E (In tote) above it that there isn't a value in Column I (Frequency) but not include the delivery Values before. So it calculates all the IN totes between the delivery days. (Column I - Frequency)

There is a chance that the delivery day could be everyday also

Please see attached screenshots as I have tried to clearly show what I want to happen. Along with color coding it so it is easy to read and understand.

I would prefer this to be done as a formula but if it has to be VBA code then I can try and get it to work as there will be multiple tables like this one.

Any other questions please let me know and I can try to provide more information.

Thank you for any help you can provide.

 

[kevin9999]

Is this it?

Book1
	A	B	C	D	E	F
1	Opening	In	Out	Close	Frequency	?
2	100	36	10	126	Tuesday	36
3	126	36	10	152		36
4	152	36	10	178	Thursday	72
5	178	36	10	204		36
6	204	0		204		36
7	204	0		204		36
8	204	31	10	225		67
9	225	31	10	246	Tuesday	98
10	246	31	10	267		31
11	267	31	10	288	Thursday	62
Sheet1

Cell Formulas
Range	Formula
F2:F11	F2	=IF(E1<>"",B2,B2+F1)

 

[Sanjeev1976]

Try the below :

Book6
	A	B	C	D	E	F	G	H	I
1	Date	Number	Opening	In	Out	Close	Frequency	Helper	Outcome
2			100	36	10	126	Tuesday	1	126
3			126	36	10	116		2	116
4			116	36	10	178	Thursday	2	178
5			178	36	10	168		3	168
6			168	0		168		3	168
7			168	0		168		3	168
8			168	31	10	158		3	158
9			158	31	10	246	Tuesday	3	246
10			246	31	10	236		4	236
11			236	31	10	288	Thursday	4	288
Sheet1

Cell Formulas
Range	Formula
I2	I2	=IF(G2<>"",C2+D2-E2)
H3:H5	H3	=IF(G3<>"",H2,H2+1)
I3:I11	I3	=IF(G3="",I2-E3,I2+SUMIF($H$2:$H$11,H3,$D$2:$D$11)-E3)
H6:H11	H6	=IF(OR(G6<>"",G5=G6),H5,H5+1)

 

[Peter_SSs]

What about this?

23 09 18.xlsm
	E	F	G	H	I
7	Opening	In	Out	Close	Frequency
8	100	36	10	126	Tuesday
9	126	36	10	116
10	116	36	10	178	Thursday
11	178	36	10	168
12	168	0		168
13	168	0		168
14	168	31	10	158
15	158	31	10	246	Tuesday
16	246	31	10	236
17	236	31	10	288	Thursday
MikeCobra

Cell Formulas
Range	Formula
E9:E17	E9	=H8
H8:H17	H8	=E8+IF(I8="",0,SUM(XLOOKUP("*y",I$7:I7,F$8:F8,,2,-1):F8))-G8