CODEWITHSUNDEEP
datetimegoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-query-language
Warren Thornton
Warren Thornton

Reputation: 305

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.

Upvotes: 1

Views: 191

Answers (1)

marikamitsos
marikamitsos

Reputation: 10573

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:

Upvotes: 1

Related Questions