During the past months while working on several Glide Apps I kept encountering the fact that Arrayformula, while incredibly practical, doesn’t work with SUMIFS() and COUNTIFS() functions.
There are basically 2 main solutions:
QUERY() is a great alternative in many cases
otherwise SUMIF() and COUNTIF() with an & to concatenate several ranges and several criteria works aswell
Yes, SUMIFS is a pain. I love both ARRAYFORMULA and QUERY but they sometimes exhibit some weird behavior that drives me up the wall.
Just today I was struggling with the QUERY always returning the first row, not matter what the filter condition. Solution was to set the header argument to 0.