Excel: Calculate total for prior rows from last 24 hours-CodePudding

I have a 3 column worksheet with columns Date, Time, Value. I need to calculate a fourth column with a running total for each row for all Value where Date Time is in the past 24 hours. The number of readings vary over the 24 hours, being as little as once a second to once every few minutes.

A SELECT would look something like:

select 
    ot.dt
    , ot.tm
    , ot.value
    , sum(it.value) 
from 
    readings ot
        inner join readings it
            on addtime(ot.dt,ot.tm) >= addtime(it.dt,it.tm)    
            and convert(timediff(addtime(ot.dt, ot.tm), addtime(it.dt, it.tm)), decimal) < 240000               
group by
    ot.dt
    , ot.tm
    , ot.value

The screenshot below gives an idea of what the resulting output should look like. Different from actual requirement is that here a reading is provided for every hr and the fourth column (SumPast3Hours) only sums the current Value and the previous two. In actuality the. number of rows to sum will vary.

CodePudding user response：

It seems ungrateful not to use your test data, but I wanted to answer the original question and consider what would happen across different days. I can think of two possible approaches:

(1) Use sumifs, one for the case where the readings are in the current day and one for the case where they are in the previous day

=SUMIFS(C$2:C2,A$2:A2,A2) SUMIFS(C$2:C2,A$2:A2,A2-1,B$2:B2,">="&B2)

(2) Use sum (or sumproduct)

=SUM(C2:C$2*((A2:A$2 B2:B$2)>=(A2 B2-1)))

This relies on the fact that dates are consecutive whole numbers, and hours are fractions (1 hour = 1/24th of a day)

You may want to change >= to > to exclude readings which are exactly 24 hours before the current reading.