Solution to: COUNTIFS and SUMIFS not working with Arrayformula

Hey Gliders :wave:

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

See example here:

Hope this helps and happy gliding :grin:


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.


Here’s a thread where I just dealt with finding a work-around for SUMIFS. The link @Karim posted was one of the sources for my solution.

1 Like