Arrayformula + Query in Google Sheets

Question

I have one workbook that has daily tracking of bidding dates and amounts and win day and amounts. In another workbook, I access that information to see the totals per day. Then play with that to see week/month/quarter and year performance. The problem I have is that I can not get my query searches to work in an array manner and have to drag the date driven formulas down 364 times for each column of information and all it's done is create a slow database...

=QUERY(importrange('source addresses'!$B$2,"bid tracker!$a$3:$x"),
"SELECT SUM (Col9) 
WHERE Col8 = date '"&text(B5,"yyyy-mm-dd")&"'
label sum(Col9) ''   ",0)

Col9 = amount of contract
Col8 = date of win

This formula produces a single data output for the sum of that days wins, I need it to array all 365 days to eliminate the other 364 of THESE formulas I am using due to my inexperience.

I have used arrayformula before query and after and every example I see doesn't seem to apply or work for me...

Share a sample workbook.

Harun24hr
– Harun24hr

2022-12-22 08:27:56 +00:00
Commented Dec 22, 2022 at 8:27 — Harun24hr
– Harun24hr, Commented Dec 22, 2022 at 8:27
use BYROW()

Ping
– Ping

2022-12-22 15:31:40 +00:00
Commented Dec 22, 2022 at 15:31 — Ping
– Ping, Commented Dec 22, 2022 at 15:31

Harun24hr · Accepted Answer · 2022-12-22 08:38:03Z

0

You need group by clause like group by Col8. Try-

=QUERY(importrange('source addresses'!$B$2,"bid tracker!$a$3:$x"), 
"SELECT SUM (Col9) 
WHERE Col8 <= date '"&text(B5,"yyyy-mm-dd")&"' AND Col8 >= date '"&text(B5-369,"yyyy-mm-dd")&
"' group by Col8 label sum(Col9) '' ",0)

answered Dec 22, 2022 at 8:38

Harun24hr

38k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Kennady Over a year ago

This definitely gave me a lot more data, BUT - It's incorrect and the data stops at row 194 (Should go to row 369). Not sure what it's doing with the data, but it's not correct. This is simplified for one date to compare rather than between multiple dates. =QUERY(importrange('source addresses'!$B$2,"bid tracker!$a$3:$x"), "SELECT SUM (Col9) WHERE Col8 = date '"&text(B5,"yyyy-mm-dd")&"' group by Col8 label sum(Col9) '' ",0) This 'group' function didn't array the data in this formula.

Tim Kennady Over a year ago

I updated the original post.

Harun24hr Over a year ago

group by clause should return 1 record for each date between WHERE clause conditions. Share a sample workbook with few dummy data.

Collectives™ on Stack Overflow

Arrayformula + Query in Google Sheets

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related