Google Spreasheet QUERY Function to Something I can use in ARRAYFORMULA

Question

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 relative and on each row are unique (C2,C3,C4,... and H2,H3,H4...)

I need to put it into ARRAYFORMULA, so it produces some result on every row

I have already spent like a full day, checking all other solutions with SUMIFS and trying to re-write the formula with MMULT, but without any success.

Here is 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(sign(column(C:C)))))

Desired column is:

CHECK
162.5
80.75
1345
656
2033
2762
1757

An if I put in each row formula: =SUMIFS(C$2:C,A$2:A,"="&A2,B$2:B,"<="&B2) it is working when manually expanding starting from 2nd

Thanks in advance.

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

Answer 1

Here's 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 converted into more general formula, which count ranges dynamically, i.e. OFFSET(A2,,,COUNTA(A2:A)).

Explanations

We use mmult function which is an array function by itself. Here's Help Center info about mmult. So we have to prepare two matrixs. First matrix with conditions, and second with Numbers, which we already have. Try entering this part or formula to get matrix with conditions:

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

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

Transpose inside it must be used to make formula expand. This is the main trick. Arrayformula can't be used to compare two vertical arrays. So you must transpose one of them. In your question we have two conditions with AND logic: A * B. So we multiply two parts of conditions. But I think, it's possible to add more then 2 conditions. If you like to make OR condition, you'll 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 count this up, but wasn't able to convert them into ArrayFormula:

1. query, formula in your question
2. sumifs
3. sumproduct
4. sum(filter(...))
5. sum(if(A*B, ... , 0))

Look at file with example