ARRAYFORMULA(VLOOKUP(QUERY(IMPORTRANGEs))) instead of ARRAYFORMULA(QUERY(IMPORTRANGEs)) doesn't work

Question

I am importing data from 4 sources, their format is:

|       C       | D |     E     | F | G |        H       |
| 31.03.2022    | * | -60 000   | * | * |   Prepayment   |
| 15.01.2022    | * |  17 087   | * | * |    Transfer    |
| 18.06.2022    | * |    -669   | * | * |     Return     |
| 15.06.2022    | * |  170 870  | * | * |     Income     |

Source spreadsheet 1

Result in the destination spreadsheet:

|      A    |    B    |
| Jun 2022  |  170870 |
| May 2022  |       0 |
| Apr 2022  |       0 |
| Mar 2022  |       0 |

In fact, the cells of column A contain 01.06.2022, 01.05.2022, etc.

The formula in B2 selects transactions for the month of June from the sources:

=--IFERROR(query(
  {
    IMPORTRANGE("Id_1", "Operations!$C$2:$H");
    IMPORTRANGE("Id_2", "Operations!$C$2:$H");
    IMPORTRANGE("Id_3", "Operations!$C$2:$H");
    IMPORTRANGE("Id_4", "Operations!$C$2:$H")
  },
    "SELECT sum(Col3) 
      WHERE 
        (Col1 <= date'"&TEXT(EOMONTH($A2,0),"yyyy-mm-dd")&"' 
        AND Col1 >= date'"&TEXT($A2,"yyyy-mm-dd")&"'
        AND Col3 >0 AND Col6 <> 'Transfer' AND Col6 <> 'Loan')
      LABEL sum(Col3) ''",0))

Similar formulas is in other cells of column B.

I want to automatically spread the formula down column B so I don't have to write the formula in every cell.

It is not possible to use ARRAYFORMULA directly. Here I read: "this is because the output of QUERY can be an array itself, so it is not possible to iterate an array output across another array". However, my formula does not output an array of numbers, but a single number, since I use sum(Col3) in it. So that's not the reason.

VLOOKUP does not work either, because column A of the destination spreadsheet contains fixed dates (the beginning of the month), and columns C of the source spreadsheets can contain any days of the month.

How to solve the problem without creating additional sheets or columns?

I recommend that you share a link to the spreadsheet itself. While your explanation makes perfect sense to you (who can see all sheets involved and knows everything about them), from out here, there are still many unknowns. In the spreadsheet, I recommend that you add a column in which you've entered the hand-entered results you expect a formula to produce. — Erik Tyler
– Erik Tyler, Commented May 14, 2022 at 23:51

player0 · Accepted Answer · 2022-05-15 01:13:56Z

1

use:

=ARRAYFORMULA(IFNA(VLOOKUP(MONTH(A2:A); QUERY({IMPORTRANGE("15BFO8u_FbibFj06FI4AsRofvxUuEc4v9gW1s4UM1pcw"; "Operations!C2:H")};
 "select month(Col1)+1,sum(Col3) where Col3 is not null and not Col6 matches 'Transfer|Loan' group by month(Col1)+1"); 2; 0)))

answered May 15, 2022 at 1:13

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Boris Baublys Over a year ago

Thanks, great solution. Didn't know about the existence of mon(). Unfortunately, your formula sums the values per month across ALL years. Perhaps there is something like year() in the query?

player0 Over a year ago

@BorisBaublys yes there is: i.sstatic.net/qYTiB.png

Boris Baublys Over a year ago

Excellent thank you. It is a pity that the year is set manually and is not extracted from column A. Thanks anyway.

Collectives™ on Stack Overflow

ARRAYFORMULA(VLOOKUP(QUERY(IMPORTRANGEs))) instead of ARRAYFORMULA(QUERY(IMPORTRANGEs)) doesn't work

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related