Google-sheets – Arrayformula and Sum queries. Possible alternatives

google sheetsgoogle-sheets-arrayformulagoogle-sheets-query

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:

enter image description here

The "Summary" Sheet summarises absences and substitutions by teacher:

enter image description here

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.