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