I am trying to use in googlesheets
a formula to group data, sum it, and use arrayformula function so that it fills a certain column.
Data is as follow:
table 1
year | quarter | amount |
---|---|---|
2022 | T4 | 4 |
2022 | T4 | 5 |
2023 | T1 | 6 |
I basically want to group by quarter and sum column amount:
table 2
year | quarter | sum |
---|---|---|
2022 | T4 | 9 |
2023 | T1 | 6 |
in table 2, col 3, in second row, I have following formula:
=query($G$11:I,"select sum(I) where G="&$L11&" and H='"&$M11&"' group by G label sum(I)'' ")
this works fine. The issue comes when I try to use arrayformula:
=arrayformula(if(L11:L<>"", query($G$11:I,"select sum(I) where G="&$L11&" and H='"&$M11&"' group by G label sum(I)'' "), "no"))
I get weird result:
year | quarter | sum |
---|---|---|
2022 | T4 | 9 |
2023 | T1 | 9 |
no | ||
no |
so the arrayformula function works fine, since col3 is filled with "no" when empty, but I don't understand why for the 2023 T1 row in table 2, the value is 9 instead of 6. I am using same query.
I've tried sumifs etc. but apparently doesn't work that well with googlesheets. I found this:
https://support.google.com/docs/thread/18015573/how-to-use-arrayformula-with-sumifs-function?hl=en
That's why I thought that with a pure query it should work.
Not sure if query is allowed, or my formula can be simplified.
I guess without the arrayformula, I am dragging down, and with the arrayformula it fulfills the column automatically, so there must be the mistake
ARRAYFORMULA can work as an array in many directions, so when it's not that clear it won't work as expected. When you have L11:L and G11:I, and the references inside QUERY that aren't an array, I suppose it takes fine the L column but not the QUERY. You could try adding L11:L and M11:M inside the QUERY.
Or more probably, try with MAP with the ranges and they'll consider them in parallel:
=MAP(L11:L,M11:M,LAMBDA(l,m,if(l<>"", query($G$11:I,"select sum(I) where G="&l&" and H='"&m&"' group by G label sum(I)'' "), "no"))))