I have the following table source of my query bets_lps
+----+-----------+--------+------------+-----------+---------------+----------+
| X | A | B | C | D | E | F |
+----+-----------+--------+------------+-----------+---------------+----------+
| 1 | Monthdate | USERID | SPENT | PURCHASED | NOT ALLOCATED | INCOME |
+----+-----------+--------+------------+-----------+---------------+----------+
| 2 | 11-2019 | USER1 | 0 | 0 | -119.37 | 11937 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 3 | 07-2019 | USER2 | -566 | -3897 | -2137 | 0 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 4 | 08-2019 | USER3 | -872 | 0 | -487 | 0 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 5 | 09-2019 | USER10 | -559 | 0 | 0 | 1231 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 6 | 07-2019 | USER5 | -9063.68 | 0 | -11500.82 | 0 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 7 | 11-2019 | USER6 | 0 | 0 | -100 | 0 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 8 | 10-2019 | USER1 | -41384.99 | -33015 | -51895.01 | 12629500 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 9 | 11-2019 | USER8 | -320316.33 | 0 | -2299.99 | 91881 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 10 | 12-2019 | USER9 | -0.01 | -4499.99 | 0 | 18000000 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 11 | 11-2019 | USER10 | 0 | 0 | -518 | 51800 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 12 | 07-2019 | USER5 | -799.32 | 0 | -3320.68 | 0 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 13 | 12-2019 | USER12 | 0 | 0 | -80 | 0 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 14 | 09-2019 | USER1 | -5820.12 | -13.1 | -2777.78 | 0 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 15 | 11-2019 | USER7 | -1637 | -35 | -182 | 185400 |
+----+-----------+--------+------------+-----------+---------------+----------+
I am trying to create an arrayformula with a query acting as sumifs to find, for each USER in the following sheet, the sum of INCOME (col F) from previous table on the specific MONTH (row 1)
+----+--------+---------+---------+---------+---------+---------+---------+
| x | A | B | C | D | E | F | G |
+----+--------+---------+---------+---------+---------+---------+---------+
| 1 | MONTH | 07-2019 | 08-2019 | 09-2019 | 10-2019 | 11-2019 | 12-2019 |
+----+--------+---------+---------+---------+---------+---------+---------+
| 2 | USER1 | | | | | | |
+----+--------+---------+---------+---------+---------+---------+---------+
| 3 | USER2 | | | | | | |
+----+--------+---------+---------+---------+---------+---------+---------+
| 4 | USER3 | | | | | | |
+----+--------+---------+---------+---------+---------+---------+---------+
| 5 | USER4 | | | | | | |
+----+--------+---------+---------+---------+---------+---------+---------+
| 6 | USER5 | | | | | | |
+----+--------+---------+---------+---------+---------+---------+---------+
| 7 | USER6 | | | | | | |
+----+--------+---------+---------+---------+---------+---------+---------+
| 8 | USER7 | | | | | | |
+----+--------+---------+---------+---------+---------+---------+---------+
| 9 | USER8 | | | | | | |
+----+--------+---------+---------+---------+---------+---------+---------+
| 10 | USER9 | | | | | | |
+----+--------+---------+---------+---------+---------+---------+---------+
| 11 | USER10 | | | | | | |
+----+--------+---------+---------+---------+---------+---------+---------+
I use this query to fetch the correct sum for each user on a specific month: example for USER1 in '07-2019':
=query(query(bets_lps!$A:$F,"select SUM(F) WHERE B='"&$A2&"' and A='"&B$1&"' GROUP BY B,A",0),"SELECT * OFFSET 1", 0)
when trying to convert query to arrayformula to avoid filling all cells, it does not expand as expected. Any suggestion ?
ADDED: Google Sheets demo (using sumifs to be turned into arrayformulas)
