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!
@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)))))}