Google Spreasheet QUERY Function to Something I can use in ARRAYFORMULA

7
votes

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

1
google-sheetsarray-formulas
You've earned the "Tumbleweed" badge (Asked a question with zero score, no answers, no comments, and low views for a week) =))) – alexsuslin
No wonder, i tried motivating myself into solving this for you but this is really boring. – Laurentiu L.
Is it even possible to do without using javascript and custom functions? (thank you for honesty, now I at least know why there are no comments) – alexsuslin
Probably. But for your sake, as well as for the readers you should restructure your question. Simplify it, make it easier to read. And in the process you will probably find a solution as well. Go to pseudocode or use diagrams if you need to. You need a clear view of the flow without loosing yourself in the details. – Laurentiu L.
I have simplified an example and even provide with test area of google spreadsheet. Simple don't know how to make it even easier. – alexsuslin

1 Answers

1
votes

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