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?


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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.