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.
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:
1 Like
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?
The QUERY formula used with the right parameters can replace the combination of ARRAYFORMULA + SUMIFS
Okay. Working on it now. Thank you!
1 Like
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.
Thank you for the feedback!
1 Like