Array cell reference in where clause of query

Question

Google Sheets sample file: https://docs.google.com/spreadsheets/d/1WqqiysWdYMAop5uQXZnisuX291wY2PFmAMEKZhya2IM/edit?usp=sharing

I am trying to calculate the average value of each person for each month. My two variables to match are the names and month of the date. Currently I am able to get the correct values if I am applying the formula to each cell using this formula:

=IFERROR(QUERY(Sheet2!A:C,"Select avg(C) where A = '"&A2&"' and month(B) = month(date '"&TEXT(DATE(2021,MONTH(DATEVALUE($A$1&"1")),1),"yyyy-mm-dd")&"') label avg(C) '' "),"-")

What I want to do is be able to do an arrayformula instead so I will only need to input the formula once and wont have to worry about the number of names entered. I tried using this formula:

=ARRAYFORMULA(IFERROR(QUERY(Sheet2!A:C,"Select avg(C) where A = '"&A2:A&"' and month(B) = month(date '"&TEXT(DATE(2021,MONTH(DATEVALUE($A$1&"1")),1),"yyyy-mm-dd")&"') label avg(C) '' "),"-"))

, but it only calculates the average for that row.

If possible, the solution should also be using query since in the data I will be using it on I am not able to use sumifs/averageifs for some reason.

user_5632629 · Answer

try:

=QUERY('data source'!A2:C, 
 "select A,avg(C) 
  where month(B)+1 = "&MONTH(A1&1)&" 
  group by A 
  label avg(C)''")

=ARRAYFORMULA({SORT(UNIQUE(FILTER('data source'!A2:A, 'data source'!A2:A<>""))), 
 IFNA(VLOOKUP(SORT(UNIQUE(FILTER('data source'!A2:A, 'data source'!A2:A<>""))), 
 QUERY('data source'!A2:C, "select A,avg(C) where month(B)+1 = "&
 MONTH(A1&1)&" group by A label avg(C)''"), 2, 0), "-")})

Array cell reference in where clause of query

1 Answers