Sum total between bottle refill times

[new11]

Hello fellow excel enthusiasts
I’m stuck trying to figure out a working formula or creating a better method to use, and was hoping that someone might be able to lend a hand, please.

I have a table, detailing the amount of water that has been consumed (in ml) thought the day, marked at hourly intervals, in column (I).
Column (J), records the time that the drink bottle was refilled with water. (I also have placed a small spot under the table to record this info, if that may work better for a formula) ​

​

My problem is, I don’t know how to be able to calculate the sum total of liquid that has been drank between the bottle refill times.​

For example, the total amount of water that has been consumed between the start of the day, 00:00:00, and the 1st bottle refill time at, 07:38:00, would be = 1,202ml.
Following from that, the total amount of water consumed between the 1st bottle refill time and the 2nd bottle refill time at, 11:02:00, would be = 1,188ml.​

Further, between the 2nd refill time, 11:02:00, and the 3rd refill time, 15:00:00, would be = 1,264ml. And so one so forth.​

​

I had tried playing around with combining if & sumifs statements, but couldn’t really think of the right direction to use.​

I’d love to hear any ideas anyone has and would greatly appreciate any help provided.
Many thanks in advance and Merry Christmas!!

dairy v1.5.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
107	Date	29-Dec-23	Water Bottle Size (ml)	1,200ml	Number of times bottle refilled			x4.0
108	Day	4
109
110	Time (24h)	heading 1	heading 2	heading 3	heading 4			Fluid Intake (ml)	Water Bottle Refill Times (24h)	Liquid Drank Between Bottle Refills	Notes	heading 5	heading 6
111	00:00:00											ml -	1,624ml
112	01:00:00							10ml				ml 10	1,634ml
113	02:00:00							200ml				ml 210	1,834ml
114	03:00:00							50ml				ml 260	1,884ml
115	04:00:00							120ml				ml 380	2,004ml
116	05:00:00							300ml				ml 680	2,304ml
117	06:00:00							222ml				ml 902	2,526ml
118	07:00:00							300ml	07:38:00	1,202ml		ml 1,202	2,826ml
119	08:00:00							264ml				ml 1,466	3,090ml
120	09:00:00							240ml				ml 1,706	3,330ml
121	10:00:00							363ml				ml 2,069	3,693ml
122	11:00:00							321ml	11:02:00	1,188ml		ml 2,390	4,014ml
123	12:00:00							564ml				ml 2,954	4,578ml
124	13:00:00							240ml				ml 3,194	4,818ml
125	14:00:00							240ml				ml 3,434	5,058ml
126	15:00:00							220ml	15:00:00	1,264ml		ml 3,654	5,278ml
127	16:00:00							342ml				ml 3,996	5,620ml
128	17:00:00							123ml				ml 4,119	5,743ml
129	18:00:00							543ml				ml 4,662	6,286ml
130	19:00:00							221ml	19:24:00	1,229ml		ml 4,883	6,507ml
131	20:00:00							240ml				ml 5,123	6,747ml
132	21:00:00							141ml				ml 5,264	6,888ml
133	22:00:00							60ml				ml 5,324	6,948ml
134	23:00:00							60ml				ml 5,384	7,008ml
135	Total		0mL					5,384mL
136
137				Water Bottle Refill Times				07:38:00	11:02:00	15:00:00	19:24:00
138				Liquid Drank Between Times				1,202ml	1,188ml	1,264ml	1,229ml	501ml
Sheet7

 

[Georgiboy]

You could try:

Cell L111:

=SCAN(0,J111:J134,LAMBDA(a,x,IF(OFFSET(x,-1,1)="",a+x,x)))

Cell J137:

=TOROW(K111:K134,1)

Cell J138:

=LET(
xl,XLOOKUP(J137#,K111:K134,L111#),
HSTACK(xl,SUM(J111:J134)-SUM(xl)))

 

[new11]

You could try:

Cell L111:

=SCAN(0,J111:J134,LAMBDA(a,x,IF(OFFSET(x,-1,1)="",a+x,x)))

Cell J137:

=TOROW(K111:K134,1)

Cell J138:

=LET(xl,XLOOKUP(J137#,K111:K134,L111#),HSTACK(xl,SUM(J111:J134)-SUM(xl)))

Hi Georgiboy,
Thanks for the reply
I've just realised that I messed up my OP, sorry.

I was meant to say that, In column K, I would like to be able to find the sum total of water drank, thats recorded in column I, between the bottle refill times listed in, column J.
At the moment in coloum K, I'm simply manually summing the range between the refill times.

I wasn't really too sure how best to word my question, but hopefully I've made a little more sense.
Cheers

 

[Georgiboy]

I thought that is what he SCAN formula I posted was doing, it creates a running total in column K that will then reset each time it see's a value in column J, therefore summing the values up to each refill point.
The only difference is the formula I have created makes a running total up to that refill point. But the value in cells K118, K122, K126, K130 will be the same as you have above.

Where I put:

Cell L111:

I should have put K111.

Maybe try clearing out column K and putting the below in K111:

=SCAN(0,J111:J134,LAMBDA(a,x,IF(OFFSET(x,-1,1)="",a+x,x)))

The other formulae were simply to catch the values from the above formula to create what you have in rows 137:138

 

[new11]

Where I put: L111 I should have put K111.
Maybe try clearing out column K and putting the below in K111:

=SCAN(0,J111:J134,LAMBDA(a,x,IF(OFFSET(x,-1,1)="",a+x,x)))

You sir are a genius and have saved me a lot of time in validation!!
I was using excel on my phone and had missed a few things in your earlier answer. But everything in your first formulas was all working tip top!!

Thanks again & Merry Christmas & a Happy NY

 

[Georgiboy]

You're welcome, thanks for the feedback & Happy NY to you too.