ARRAYFORMULA, VLOOKUP, QUERY and AND together

Ask for Help
1

Hi guys,
I am looking for your Google Sheets expertise.

I’m trying to combine ARRAYFORMULA, VLOOKUP, QUERY together.

If I take off the “AND Col1 <= DATE '”&TEXT(F2:F,“yyyy-mm-dd”)&" " part it works perfectly, but if I do want to include it, the first row is the only row that works as expected.

=ARRAYFORMULA(VLOOKUP(
A2:A,
QUERY(
{‘Income’!A2:A&‘Income’!C2:C,‘Income’!B2:B},
“SELECT Col1, SUM(Col2) WHERE Col1 >= DATE '”&TEXT(E2:E,“yyyy-mm-dd”)&“’ AND Col1 <= DATE '”&TEXT(F2:F,“yyyy-mm-dd”)&“’ GROUP BY Col1”,
0
),
2,
0
))

Happy to hear your thoughts.

2

Hola,

The Arrayformula(Query(…)) combination is not supported in GS, never will work so you have to think differently about the solution.

If you give us an example with some images associated to what you want to get, we surely might find a workaround.

Saludos!

3

Thanks my friend,
The thing is, it does work if I remove the AND part from my WHERE statement…

4

In arrayformula, you can’t use AND logic… use * to multiply values… if the result is not 0… then it is a match…
also… arrayformula will not work with filters (it will only process the first value)… as @gvalero said, you have to create workaround using vlookups and if formulas

5

I don’t know, maybe knowing better what you try to do we can modify or create a better Query statement and remove the VLOOKUP() from your formula.

Bye

6

Can you provide a sample sheet with dummy data, and tell me what’s your expected result so I can dive in and try?

Also, have you considered moving this logic to a Glide calculated column, or do you need to see it in the Sheets?

2 Likes
7

This :point_up:

Maybe this will help?

1 Like
8

Thanks guys!

Calculating this over Glide is quite straight forward, but I do need it in excel for cross reference etc. :zipper_mouth_face:

A bit about what I’m trying to do:
Table A is a log of all my incomes: Row ID | Category ID | Value | Transaction date

Table B is a smart summary of the transaction sum, grouped by the category ID and transaction period:
Row ID | Category ID | Expected income | From date | To date

In table B, the same category can point to different periods (From → To dates), and the matching incomes from table A (both Category ID and date period) should be aggregated.

Hope this was clear enough

9

So you log all your incomes into table A, and table B is where you want to combine those logs into certain periods grouped by category? Is “Expected income” the column where the formula should be used?

10

I log all my incomes into Table A, and Table B is where I combine those logs into certain periods grouped by category IDs.

Expected income is another numeric value (not calculated) that I add/ subtract from the grouped value as I tried to calculate it.

11

Please check this and let me know if it works.

Using BYROW, which I always use instead of arrayformula now.

1 Like
12

OMG,
IT WORKED! :slight_smile:

Thank you so much

1 Like
13

Great to hear!

14

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.