Search code examples
datetimegoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-query-language

Google Query to Sum Amount between two Timestamps

So I am trying to use the Query to sum the total amount of all transactions during a time period. Here is a sample of the time stamp:

Column A:                       Column M:

02-Mar-2020 07:29 PM PST        5.00                
02-Mar-2020 07:21 PM PST        6.23            
02-Mar-2020 07:18 PM PST        4.25                
02-Mar-2020 07:15 PM PST        5.00                
02-Mar-2020 07:09 PM PST        4.25            
02-Mar-2020 07:08 PM PST        5.00                
02-Mar-2020 07:01 PM PST        6.23                
02-Mar-2020 07:00 PM PST        4.25                
02-Mar-2020 06:44 PM PST        6.23                
02-Mar-2020 06:38 PM PST        6.23                
02-Mar-2020 06:37 PM PST        4.25                
02-Mar-2020 06:09 PM PST        5.00            
02-Mar-2020 06:08 PM PST        5.00                
02-Mar-2020 06:06 PM PST        6.23                
02-Mar-2020 06:05 PM PST        6.23

I'd like to be able to sum the values of column M between say 6:15pm and 7:00pm. Should I use filter first? I looked into doing this but it requires some combination of &text and number formatting, so I figured Id ask here to give me an idea how to apply this to other specific ranges.

Solution

  • You can certainly use a query.
    Still. In this case, I think that using SUMIFS is easier. 

    =SUMIFS(M2:M22,ArrayFormula((REGEXEXTRACT(A2:A22,"\d\d:\d\d PM"))*1),">18:15", 
               ArrayFormula((REGEXEXTRACT(A2:A22,"\d\d:\d\d PM"))*1),"<=19:00")

    ArrayFormula((REGEXEXTRACT(A2:A22,"\d\d:\d\d PM"))*1)
    We use REGEXEXTRACT to extract the time from our cells which (because it is extracted as text) we multiply by 1 to turn it to a number.
    We then use the SUMIFS function to sum the cells needed.

    enter image description here

    Functions used: