Transform SUMIFS into SUMIF (or anything else) to make it work with ARRAYFORMULA-CodePudding

I am trying to automatize properly a spreadsheet (i.e. not having to manually expand formulas) and I can't figure out a workaround when it comes to SUMIFS.

As far as I searched online, I can't use either SUMIFS or QUERY in an ARRAYFORMULA. I read it was possible to do it through SUMIF but haven't succeeded.

Rather than having that on each line: =SUMIFS(C:C,A:A,"="&A2,B:B,"<="&B2)

I am trying to have 1 formula on the first line. I thought the one below would work, but it didn't. ={"total_sessions_per_install";ARRAYFORMULA(IF(NOT(ISBLANK(A2:A)),SUMIF(A:A&B:B,"="&A2:A&"<="&B2:B,C:C),IFERROR(0/0)))}

Here is a demo file: https://docs.google.com/spreadsheets/d/1lI_XABPwXb4Gc_cb9gjBrDKMaL_Aj0RLS3BTISMLyhA/edit?usp=sharing

Thanks for the help!

CodePudding user response：

use:

=INDEX(MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(
 INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(
 INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))))*(
 INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(
 INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))), 
 INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(
 INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))^0))

CodePudding user response：

or slower one if you got a lot of blank rows:

=ARRAYFORMULA(SUMIF(ROW(B2:B79), "<="&ROW(B2:B79), C2:C79)-
 SUMIF(A2:A79, "<"&A2:A79, C2:C79))