I want to use this query as an arrayformula

1
votes

Can anyone suggest to me how I can convert this query into something that will work in an arrayformula?

=iferror(QUERY(importorders!A:H,"Select count(A) where C = 'Thailand Tour' and month(H) = "&MONTH(A3)-1&"and year(H) = "&year(A3)&" label count(A) ''",1),0)

It's basically just counting to see how many orders I had in each month.

Thanks for looking.

View sheet (image)

Sheet: https://docs.google.com/spreadsheets/d/1Of6cdFYaOzCFwPdZ4ABItD6dghMjHhafRWmDJWaznbg/edit#gid=711075203

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
share a copy of your sheet with fake data – player0
Ok I've created a copy sheet: docs.google.com/spreadsheets/d/… – user198561

2 Answers

0
votes

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A3:A, QUERY({importorders!A2:C, 
 EOMONTH(importorders!H2:H, -1)+1},
 "select Col4,count(Col1) 
  where Col3 = 'Thailand Tour' 
    and Col4 is not null 
  group by Col4
  label count(Col1)''", 0), 2, 0), 0))

0

0
votes

You have to use group by

To get just for Thailand Tour

=iferror(QUERY(importorders!A:H,"Select count(A) where C = 'Thailand Tour' and month(H) = "&MONTH(A3)-1&"and year(H) = "&year(A3)&" group by C label count(A) ''",1),0)

To get results for the whole column try this

=iferror(QUERY(importorders!A:H,"Select count(A) where month(H) = "&MONTH(A3)-1&"and year(H) = "&year(A3)&" group by C label count(A) ''",1),0)

(A demo sheet with expected results would be really helpful)