# Copy SUMIFS to all columns (Using QUERY vs ARRAYFORMULA) **URL:** \ **Category:** Ask for Help\ **Created:** [December 1, 2019, 7:41pm UTC](https://community.glideapps.com/t/copy-sumifs-to-all-columns-using-query-vs-arrayformula/2561 "2019-12-01T19:41:03Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Olivia\_Green](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/olivia_green/32/1642_2.png) [@Olivia\_Green](https://community.glideapps.com/u/Olivia_Green)\ **Post date:** [December 1, 2019, 7:41pm UTC](https://community.glideapps.com/t/copy-sumifs-to-all-columns-using-query-vs-arrayformula/2561/1 "2019-12-01T19:41:03Z")
I have a formula that I need to drop down automatically when a new row is added. I have tried to use an arrayformula which works for me in other scenarios. However, the formula that is in row 2 is complex and specifically references row 2. So when using an arrayformula it doesn’t reference the subsequent rows when added (row 3,4,5,6…). If I do an arrayformula in this case it will constantly give me the sum or value from row 2. I want the formula to act as if I dragged the formula down manually -mainly for how it changes the cell reference. ![52 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/9/9b72a62bd0b7a0d18dfb927dd136ad746ccdfcc5.png) ---
**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:** [December 2, 2019, 1:58am UTC](https://community.glideapps.com/t/copy-sumifs-to-all-columns-using-query-vs-arrayformula/2561/2 "2019-12-02T01:58:29Z")
Hi Olivia, Indeed ARRAYFORMULA doesn’t work with SUMIFS. Your best bet (works perfectly for me) is using the QUERY formula. Here’s a great resource that helped me a lot: > **[Google Sheets Query function: Learn the most powerful function in Sheets](https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/)** > > Learn how to use the super-powerful Google Sheets Query function to bring the power of SQL to your data, with this comprehensive tutorial and available template. ---
**Author:** ![Olivia\_Green](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/olivia_green/32/1642_2.png) [@Olivia\_Green](https://community.glideapps.com/u/Olivia_Green)\ **Post date:** [December 2, 2019, 2:35am UTC](https://community.glideapps.com/t/copy-sumifs-to-all-columns-using-query-vs-arrayformula/2561/3 "2019-12-02T02:35:32Z")
Thank you for this information. Just so I’m clear, are you suggesting I remove the SUMIFS formula and use the Query formula instead? Then it might work with the arrayformula? ---
**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:** [December 2, 2019, 3:39am UTC](https://community.glideapps.com/t/copy-sumifs-to-all-columns-using-query-vs-arrayformula/2561/4 "2019-12-02T03:39:18Z")
The QUERY formula used with the right parameters can replace the combination of ARRAYFORMULA + SUMIFS ---
**Author:** ![Olivia\_Green](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/olivia_green/32/1642_2.png) [@Olivia\_Green](https://community.glideapps.com/u/Olivia_Green)\ **Post date:** [December 2, 2019, 3:40am UTC](https://community.glideapps.com/t/copy-sumifs-to-all-columns-using-query-vs-arrayformula/2561/5 "2019-12-02T03:40:10Z")
Okay. Working on it now. Thank you! ---
**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:** [December 8, 2019, 5:22am UTC](https://community.glideapps.com/t/copy-sumifs-to-all-columns-using-query-vs-arrayformula/2561/6 "2019-12-08T05:22:46Z")
I’ve ran into this, and as stated, SUMIFS are not compatible with ARRAYFORMULA, however SUMIF is compatible, so if there is any other way to structure your condition into one single condition, then you would be fine. In my situation I use SUMIFS and have dragged it down 2000 rows in row D for example. Then I use a QUERY to fill rows A-C with the data from another sheet. The results of the query are used to figure out the SUMIFS conditions. I also have a similar situation like yours where I generate invoices based off of a billing cycle with a beginning and end date. The calculations for the total billing amount happens in a different sheet and I use a VLOOKUP consisting of email, from and to date as one value to retrieve the matching value in another sheet that contains the the same combined value. It’s really hard to explain what I’m doing, but there are ways around it. ---
**Author:** ![Olivia\_Green](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/olivia_green/32/1642_2.png) [@Olivia\_Green](https://community.glideapps.com/u/Olivia_Green)\ **Post date:** [December 8, 2019, 5:49am UTC](https://community.glideapps.com/t/copy-sumifs-to-all-columns-using-query-vs-arrayformula/2561/7 "2019-12-08T05:49:06Z")
Thank you for the feedback!