2

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

player0
  • 124,011
  • 12
  • 67
  • 124
delimiter
  • 745
  • 4
  • 13
  • Found some relevant info about the same thing here https://webapps.stackexchange.com/questions/97587/how-can-i-merge-two-columns-by-using-query/97658#97658 – delimiter Jan 15 '20 at 18:39

2 Answers2

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

0

player0
  • 124,011
  • 12
  • 67
  • 124
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) ''"
    )
  )
user11982798
  • 1,878
  • 1
  • 6
  • 8
  • 1
    Thank you for this. Actually accepted your answer for 2 methods although another answer had some visuals. – delimiter Jan 15 '20 at 18:38