How to increment date in arrayformula sumifs in google sheets?

0
votes

I have adapted a formula from another post and it works, yet I need to increment the formula down hundreds of rows, but I cannot figure out how to get the dates to increase.

I have a Google Form that sends data to its spreadsheet. There may be several entries per date and I want the data to be summed on the 2nd sheet before I graph it. 

Data
 A                     B
6/3/2016 06:06:00      2
6/3/2016 11:06:00      2
6/4/2016 07:05:00      1
6/4/2016 09:10:00      5

Result
 A   B
6/3  4
6/4  

B2 =ArrayFormula(SUM(If((Data!A$2:A<DATE(2016,6,5))*(Data!A$2:A>DATE(2016,6,4)),Data!B$2:B)))

I will have dates that run the entire year, so I need an easy way to increment the dates. Also, if you have a way to grab data from the exact date needed, that would be nice too (instead of giving the date range).

1
dategoogle-sheets

1 Answers

1
votes

A query formula may work better in this example, such as:

=QUERY({ARRAYFORMULA(TRUNC('Form responses 1'!A2:A)),'Form responses 1'!B2:B},"select Col1,sum(Col2) where Col1 >0 group by Col1 label Col1'Date' format Col1 'mm-dd'")

Amend the sheet name and ranges as appropriate