Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language

Marrying Query and Arrayformula through cell reference in Google Sheets

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

Solution

  • 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) ''"
    )
  )