# Solution to: COUNTIFS and SUMIFS not working with Arrayformula

**URL:** <https://community.glideapps.com/t/solution-to-countifs-and-sumifs-not-working-with-arrayformula/1824>\
**Category:** Ask for Help\
**Created:** [November 9, 2019, 3:09pm UTC](https://community.glideapps.com/t/solution-to-countifs-and-sumifs-not-working-with-arrayformula/1824 "2019-11-09T15:09:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Karim](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/karim/32/13974_2.png) [@Karim](https://community.glideapps.com/u/Karim)\
**Post date:** [November 9, 2019, 3:09pm UTC](https://community.glideapps.com/t/solution-to-countifs-and-sumifs-not-working-with-arrayformula/1824/1 "2019-11-09T15:09:37Z")

</div>

Hey Gliders 👋

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:

> <https://stackoverflow.com/questions/30081491/google-sheets-arrayformula-with-sumifs>

Hope this helps and happy gliding 😁

---

<div class="post-metadata">

**Author:** ![Doomba](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/doomba/32/1133_2.png) [@Doomba](https://community.glideapps.com/u/Doomba)\
**Post date:** [November 9, 2019, 4:41pm UTC](https://community.glideapps.com/t/solution-to-countifs-and-sumifs-not-working-with-arrayformula/1824/2 "2019-11-09T16:41:56Z")

</div>

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.

> <https://stackoverflow.com/questions/54343018/google-sheets-query-where-clause-just-returns-first-row-of-data>

---

<div class="post-metadata">

**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\
**Post date:** [November 11, 2019, 7:18pm UTC](https://community.glideapps.com/t/solution-to-countifs-and-sumifs-not-working-with-arrayformula/1824/3 "2019-11-11T19:18:14Z")

</div>

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.

> [@\[HELP NEEDED\] Building A Sales Leaderboard](https://community.glideapps.com/t/help-needed-building-a-sales-leaderboard/1789):
>
> Hello, I am trying to build a sales leaderboard and I’m almost done with it, but there’s this one part that I cannot figure out with ARRAYFORMULAS. I’m trying to make it so that whenever a new row is added, a formula is automatically applied to the new row. I don’t want to create 10,000 rows and then drag the formula all the way down 10,000 rows because it displays all weird in the app and it’s not efficient. I’ve used the ARRAY formula on the ‘Stat Tracker’ sheet within the Google Sheet in th…