I am using a sheet to list and summarise teachers absences and substitutions. Here's the worksheet
Basically in the sheet "Substitutions" all absences and substituions are stored record by record:
The "Summary" Sheet summarises absences and substitutions by teacher:
Summarising is done by month using the following formula:
=iferror(query(Substitutions!$A$3:$H$21;"select sum (E) where( (H) = 'M' AND month(B) = " & month(F$3)-1 & " AND (A) = '" & $A6 & "' AND (C) = 'ASS') label Sum (E)''"))/1440
As this calculation is done cell by cell and row by row (columns F to N), I'd like to use arrayformula to replicate the same formula across the rows, however it looks like arrayformula
doesn't expand properly "sum" in queries.
How can I modify the above formula to be used with arrayformula
?
Best Answer
QUERY doesn't work with an array as it second argument, so your formula can't be used as an array formula.
Instead you should figured out a formula that could achieve the same result that uses functions that could use arrays as arguments. As an alternative you could create a custom function by using Google Apps Script.