Marrying Query and Arrayformula through cell reference in Google Sheets

Question

Trying to understand if it is possible to apply ARRAYFORMULA to situations when QUERY is used in Google Sheets.

For example, I used QUERY for querying and aggregating a set of items, like so:

=QUERY($H$2:$I$17,"select sum(I) where H='"&A2&"' label sum(I) ''",0)

But in order to make that work across the spreadsheet, I will have to drag this formula down. There is also the ARRAYFORMULA thing, which is supposed to help with getting rid of excessive dragging, however it does not seem to work with QUERY, or am I just missing something?

A picture for a quick look:

And a shared file for the longer thinking:

https://docs.google.com/spreadsheets/d/1xOdqeESrFbrBknNYahSeF0ripA5fr2vVFQ-r--lkdA0/edit?usp=sharing

Found some relevant info about the same thing here webapps.stackexchange.com/questions/97587/… — delimiter
– delimiter, Commented Jan 15, 2020 at 18:39

player0 · Accepted Answer · 2020-01-13 23:20:05Z

3

use this formula:

=QUERY(H2:I17, "select H,sum(I) where H is not null group by H label sum(I)''", 0)

and then you can do simple VLOOKUP like:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, QUERY(H2:I17, 
 "select H,sum(I) where H is not null group by H label sum(I)''", 0), 2, 0)))

answered Jan 13, 2020 at 23:20

player0

131k14 gold badges91 silver badges149 bronze badges

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

Comments

user11982798 · Accepted Answer · 2020-01-14 16:16:12Z

1

Here two method alternatively:

first ==>

=arrayformula(sumif(H2:H,"=" & unique(filter(H2:H,H2:H<>"")),I2:I))

second ==>

=arrayformula(
   query(
     filter({vlookup(H2:H,{A2:A,row(A2:A)},2,false),H2:I},H2:H<>"")
     ,"Select sum(Col3) group by Col1 label Sum(Col3) ''"
    )
  )

answered Jan 14, 2020 at 16:16

user11982798

1,8981 gold badge8 silver badges8 bronze badges

1 Comment

delimiter Over a year ago

Thank you for this. Actually accepted your answer for 2 methods although another answer had some visuals.

Collectives™ on Stack Overflow

Marrying Query and Arrayformula through cell reference in Google Sheets

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related