Formula for a rolling 24 hours total?

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
Using the sample dataset below, how can I get a rolling 24 hour total in that third column (the values below show the correct totals from the previous 24 hours)

DateValueRolling Total over the last 24 hours
5/23/2020 12:00:00 PM350350
5/23/2020 2:00:00 PM86436
5/23/2020 5:00:00 PM427863
5/23/2020 6:00:00 PM2701133
5/23/2020 7:00:00 PM3051438
5/23/2020 8:00:00 PM3801818
5/23/2020 9:00:00 PM171835
5/23/2020 10:00:00 PM2132048
5/23/2020 11:00:00 PM3172365
5/24/2020 12:00:00 AM2892654
5/24/2020 1:00:00 AM2612915
5/24/2020 2:00:00 AM2683183
5/24/2020 7:00:00 AM773260
5/24/2020 8:00:00 AM1933453
5/24/2020 9:00:00 AM3343787
5/24/2020 10:00:00 AM3814168
5/24/2020 11:00:00 AM2334401
5/24/2020 12:00:00 PM334434
5/24/2020 1:00:00 PM1304214
5/24/2020 2:00:00 PM354249
5/24/2020 3:00:00 PM1704333
5/24/2020 4:00:00 PM1144447
5/24/2020 8:00:00 PM2213666
5/24/2020 9:00:00 PM3523638
5/24/2020 10:00:00 PM3203941
5/24/2020 11:00:00 PM4664194
5/25/2020 12:00:00 AM1674044
5/25/2020 5:00:00 AM3223548
5/25/2020 6:00:00 AM3713919
5/25/2020 7:00:00 AM1504069
5/25/2020 8:00:00 AM174009
5/25/2020 9:00:00 AM3944210
5/25/2020 10:00:00 AM2884164
5/25/2020 11:00:00 AM1253908
 
This should do it

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SUMPRODUCT(--($A$2:$A2>=$A2-1),$B$2:$B2)[/FONT]
 
Actually, SUMIF is better, more efficient

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SUMIF($A$2:$A2,">="&$A2-1,$B$2:$B2)[/FONT]
 
Perfect...I had a feeling SUMPRODUCT() would be used here, but I'm not efficient in constructing the correct syntax of that formula. I do like the use of the SUMIF() better as I can deconstruct the logic out of that better.
 
Have a look at Bob's page about the SUMPRODUCT function. It's very well explained. This function is very versatile
 
Back
Top