<\/script>')
Geek Answers Handbook

Google Spreasheet QUERY Function for what I can use in ARRAYFORMULA

I have the following query function

=QUERY(C:J,"SELECT SUM(J) WHERE C='"&C2&"' AND H<=date'"&TEXT(H2,"yyyy-MM-dd")&"' LABEL SUM(J) ''",0)

WHERE C2 and H2 are relative and unique on each line (C2, C3, C4, ... and H2, H3, H4 ...)

I need to put it in ARRAYFORMULA, so it gives some result for each row

I already spent the whole day checking all the other solutions using SUMIFS and trying to rewrite the formula using MMULT, but without any success.

Here is a simplified example:

 Proj Date Hours APROJ 6/29/2015 81.75 APROJ 6/22/2015 80.75 BPROJ 8/3/2015 689 BPROJ 8/2/2015 656 BPROJ 8/10/2015 688 BPROJ 8/11/2015 729 CPROJ 8/12/2015 1757

My attempt without success: = arrayformula (mmult ((A: A = A1: A) * (B: B <= B1: B) * N (C: C), transpose (character (column (column (C: C))) )))

Column Required:

 CHECK 162.5 80.75 1345 656 2033 2762 1757

An, if I put in each formula the lines: = SUM (C $ 2: C, A $ 2: A, "=" & A2, B $ 2: B, "<=" & B2), it works when the manual extension starting from the second

Thanks in advance.

If you want to play, just copy it yourself: https://docs.google.com/spreadsheets/d/12F4EsHvkiZb5gAPVo_uosd2YpZ1nw9QED_JlSAcVQYU/edit?usp=sharing

+7
google-spreadsheet array-formulas
source share
1 answer

Here is the answer:

 =MMULT(TRANSPOSE(ArrayFormula(--($A$2:$A$8=TRANSPOSE(A2:A8))*--($B$2:$B$8<=TRANSPOSE(B2:B8)))),C2:C8)

This formula can be transformed into a more general formula that is calculated dynamically, i.e. OFFSET(A2,,,COUNTA(A2:A)) .

Explanation

We use the mmult function, which in itself is an array function. Here's the Help Center Help for mmult . Therefore, we must prepare two matrices. The first matrix with conditions and the second with numbers that we already have. Try entering this part or formula to get a matrix with the conditions:

 =ArrayFormula(--($A$2:$A$8=TRANSPOSE(A2:A8))*--($B$2:$B$8<=TRANSPOSE(B2:B8)))

In this part, we get a table / matrix with Zoros and Ones. I prefer to use '-' to convert Boolaen to Integer: related question

The transpose inside it should be used to expand the formula. This is the main trick. Arrayformula cannot be used to compare two vertical arrays. Therefore, you must transfer one of them. In your question, we have two conditions with the logic AND: A * B. Therefore, we multiply the two parts of the conditions. But I think that more than two conditions can be added. If you like to make the OR condition, you need to add them A + B. Some examples:

  • (A + B) * C - [A or B] AND C
  • A + B * C - A OR [B and C]

Conclusion

It was a real challenge for me. By the way, I found 5 different formulas to calculate this, but could not convert them to ArrayFormula:

  • request, the formula in your question
  • SUMIFS
  • SUMPRODUCT
  • amount (filter (...))
  • sum (if (A * B, ..., 0))

See file with an example

+1
source share

More articles:


All Articles