Marrying Query and Arrayformula through cell reference in Google Sheets

1
votes

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:

query and arrayformula

And a shared file for the longer thinking:

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

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language
Found some relevant info about the same thing here webapps.stackexchange.com/questions/97587/… – delimiter

2 Answers

1
votes

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) ''"
    )
  )
2
votes

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)))

0