How to add a query with where date condition in a arrayformula?

Question

I need to add a query in arrayformula. My data:

	A	B	C	D	E	F
1	DATE	ITEM	TRANSACTION	PRICE	QTY	ARRAYFORMULA???
2	2021/06/30	aaaa	verify	14.00	1	74.00 ((5-0)*14+4)
3	2021/06/30	bbbb	verify	10.00	1	202.00 ((40-20)*10+2)
4	2021/06/30	bbbb	sell	15.00	20	-
5	2021/06/10	aaaa	received	4.00	1	-
6	2021/05/31	aaaa	verify	10.00	1	50.00 ((5-0)*10+0)
7	2021/05/31	bbbb	verify	8.00	1	322.00 ((40-0)*8+2)
8	2021/05/20	bbbb	received	2.00	1	-
9	2021/05/10	bbbb	buy	8.00	40	-
10	2021/05/09	aaaa	buy	11.00	5	-

Basically I need in column F: if transaction is "verify", return the value received for that item PLUS the PRODUCT between the quantity in stock (column E where column C is "buy" MINUS column E where column C is "sell") and the current price (column D) until that date. I think I need 3 queries: one for received values, another for quantity bought and the last for quantity sold. I'm stuck in the first query:

=SUM(QUERY(A2:E;"SELECT SUM(D) WHERE A <= date '"&TEXT(DATEVALUE(A2);"yyyy-mm-dd")&"' AND C = 'received' AND B = '"&B2&"'"))

Another approach:

=vlookup(B2;QUERY(A2:E;"SELECT B, SUM(D) WHERE A <= date '"&TEXT(DATEVALUE(A2);"yyyy-mm-dd")&"' AND C = 'received' AND B = '"&B2&"' group by B");2;false)+(vlookup(B2;QUERY(A2:E;"SELECT B, SUM(E) WHERE A <= date '"&TEXT(DATEVALUE(A2);"yyyy-mm-dd")&"' AND C = 'buy' AND B = '"&B2&"' group by B");2;false)-iferror(vlookup(B2;QUERY(A2:E;"SELECT B, SUM(E) WHERE A <= date '"&TEXT(DATEVALUE(A2);"yyyy-mm-dd")&"' AND C = 'sell' AND B = '"&B2&"' group by B");2;false);0))*D2

I can't add this in a arrayformula (cell F1). Btw I need a arrayformula because I have a lot of data. Sorry my English. Thanks for your help!

Answer 1

@Max Makhrov posted an answer is this link using "MMULT(TRANSPOSE(" to FILTER in ARRAYFORMULA. I solved my question with the below arrayformula (link):

={"ARRAYFORMULA";ARRAYFORMULA(IF(C2:C<>"verify";"";MMULT(--(TRANSPOSE(A2:A)<=A2:A)*(TRANSPOSE(B2:B)=B2:B)*(TRANSPOSE(C2:C)="received");if(D2:D="";0;D2:D))+if(D2:D="";0;D2:D)*(MMULT(--(TRANSPOSE(A2:A)<=A2:A)*(TRANSPOSE(B2:B)=B2:B)*(TRANSPOSE(C2:C)="buy");if(E2:E="";0;E2:E))-MMULT(--(TRANSPOSE(A2:A)<=A2:A)*(TRANSPOSE(B2:B)=B2:B)*(TRANSPOSE(C2:C)="sell");if(E2:E="";0;E2:E)))))}

Answer 2

try:

={"ARRAYFORMULA"; ARRAYFORMULA(IFNA((VLOOKUP(COUNTIFS(C2:C, C2:C, C2:C, "verify", ROW(C2:C), "<="&ROW(C2:C))&"♦"&B2:B&"♦buy", QUERY(SPLIT(FLATTEN(IFERROR(SEQUENCE(COUNTIF(C:C, "verify"))&"♦"&VLOOKUP(
IF(SEQUENCE(COUNTIF(C:C, "verify"), COUNTA(C:C))-(SEQUENCE(COUNTIF(C:C, "verify"), 1, 0)*COUNTA(C:C))>FILTER(ROW(A:A), C:C="verify"), SEQUENCE(1, COUNTA(C:C)), )&FILTER(B:B, C:C="verify"), 
{ROW(A:A)&B:B, B:B&"♦"&C:C&"×"&D:D&"×"&E:E}, 2, 0))), "×"), "select Col1,sum(Col2),sum(Col3) where Col2 is not null group by Col1"), 3, 0)-
 IFNA(VLOOKUP(COUNTIFS(C2:C, C2:C, C2:C, "verify", ROW(C2:C), "<="&ROW(C2:C))&"♦"&B2:B&"♦sell", QUERY(SPLIT(FLATTEN(IFERROR(SEQUENCE(COUNTIF(C:C, "verify"))&"♦"&VLOOKUP(
IF(SEQUENCE(COUNTIF(C:C, "verify"), COUNTA(C:C))-(SEQUENCE(COUNTIF(C:C, "verify"), 1, 0)*COUNTA(C:C))>FILTER(ROW(A:A), C:C="verify"), SEQUENCE(1, COUNTA(C:C)), )&FILTER(B:B, C:C="verify"), 
{ROW(A:A)&B:B, B:B&"♦"&C:C&"×"&D:D&"×"&E:E}, 2, 0))), "×"), "select Col1,sum(Col2),sum(Col3) where Col2 is not null group by Col1"), 3, 0), 0))*
 IF(C2:C="verify", D2:D, )+
 IFNA(VLOOKUP(COUNTIFS(C2:C, C2:C, C2:C, "verify", ROW(C2:C), "<="&ROW(C2:C))&"♦"&B2:B&"♦received", QUERY(SPLIT(FLATTEN(IFERROR(SEQUENCE(COUNTIF(C:C, "verify"))&"♦"&VLOOKUP(
IF(SEQUENCE(COUNTIF(C:C, "verify"), COUNTA(C:C))-(SEQUENCE(COUNTIF(C:C, "verify"), 1, 0)*COUNTA(C:C))>FILTER(ROW(A:A), C:C="verify"), SEQUENCE(1, COUNTA(C:C)), )&FILTER(B:B, C:C="verify"), 
{ROW(A:A)&B:B, B:B&"♦"&C:C&"×"&D:D&"×"&E:E}, 2, 0))), "×"), "select Col1,sum(Col2),sum(Col3) where Col2 is not null group by Col1"), 2, 0), 0)))}

---

non-english locale:

={"ARRAYFORMULA"; ARRAYFORMULA(IFNA((VLOOKUP(COUNTIFS(C2:C; C2:C; C2:C; "verify"; ROW(C2:C); "<="&ROW(C2:C))&"♦"&B2:B&"♦buy"; QUERY(SPLIT(FLATTEN(IFERROR(SEQUENCE(COUNTIF(C:C; "verify"))&"♦"&VLOOKUP(
IF(SEQUENCE(COUNTIF(C:C; "verify"); COUNTA(C:C))-(SEQUENCE(COUNTIF(C:C; "verify"); 1; 0)*COUNTA(C:C))>FILTER(ROW(A:A); C:C="verify"); SEQUENCE(1; COUNTA(C:C)); )&FILTER(B:B; C:C="verify"); 
{ROW(A:A)&B:B\ B:B&"♦"&C:C&"×"&D:D&"×"&E:E}; 2; 0))); "×"); "select Col1,sum(Col2),sum(Col3) where Col2 is not null group by Col1"); 3; 0)-
 IFNA(VLOOKUP(COUNTIFS(C2:C; C2:C; C2:C; "verify"; ROW(C2:C); "<="&ROW(C2:C))&"♦"&B2:B&"♦sell"; QUERY(SPLIT(FLATTEN(IFERROR(SEQUENCE(COUNTIF(C:C; "verify"))&"♦"&VLOOKUP(
IF(SEQUENCE(COUNTIF(C:C; "verify"); COUNTA(C:C))-(SEQUENCE(COUNTIF(C:C; "verify"); 1; 0)*COUNTA(C:C))>FILTER(ROW(A:A); C:C="verify"); SEQUENCE(1; COUNTA(C:C)); )&FILTER(B:B; C:C="verify"); 
{ROW(A:A)&B:B\ B:B&"♦"&C:C&"×"&D:D&"×"&E:E}; 2; 0))); "×"); "select Col1,sum(Col2),sum(Col3) where Col2 is not null group by Col1"); 3; 0); 0))*
 IF(C2:C="verify"; D2:D; )+
 IFNA(VLOOKUP(COUNTIFS(C2:C; C2:C; C2:C; "verify"; ROW(C2:C); "<="&ROW(C2:C))&"♦"&B2:B&"♦received"; QUERY(SPLIT(FLATTEN(IFERROR(SEQUENCE(COUNTIF(C:C; "verify"))&"♦"&VLOOKUP(
IF(SEQUENCE(COUNTIF(C:C; "verify"); COUNTA(C:C))-(SEQUENCE(COUNTIF(C:C; "verify"); 1; 0)*COUNTA(C:C))>FILTER(ROW(A:A); C:C="verify"); SEQUENCE(1; COUNTA(C:C)); )&FILTER(B:B; C:C="verify"); 
{ROW(A:A)&B:B\ B:B&"♦"&C:C&"×"&D:D&"×"&E:E}; 2; 0))); "×"); "select Col1,sum(Col2),sum(Col3) where Col2 is not null group by Col1"); 2; 0); 0)))}