1

I would like to sum the values ​​of a table grouped by months but adding up the previous months.

Example:

Example


Result that I want:

Result

The closest I've come so far is:

=Query(B2:C4;"select sum(C), B group by B"; -1)
0

=QUERY({B2:C}; "select Col1,sum(Col2) 
                where Col1 !='' 
                group by Col1 
                label sum(Col2)''"; 0)

0

0


=ARRAYFORMULA({"Month"\ "Total"; {
 QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2) 
                      where toDate(Col1) is not null 
                      group by toDate(Col1) 
                      label sum(Col2)'',toDate(Col1)''"; 0); "select Col1"; 0)\ 
 MMULT(TRANSPOSE((ROW(INDIRECT("A1:A"&COUNTA(
 QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2) 
                      where toDate(Col1) is not null 
                      group by toDate(Col1) 
                      label sum(Col2)'',toDate(Col1)''"; 0); "select Col2"; 0))))<=
 TRANSPOSE(ROW(INDIRECT("A1:A"&COUNTA(
 QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2) 
                      where toDate(Col1) is not null 
                      group by toDate(Col1) 
                      label sum(Col2)'',toDate(Col1)''"; 0); "select Col2"; 0))))))*
 QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2) 
                      where toDate(Col1) is not null 
                      group by toDate(Col1) 
                      label sum(Col2)'',toDate(Col1)''"; 0); "select Col2"; 0)); SIGN(
 QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2) 
                      where toDate(Col1) is not null 
                      group by toDate(Col1) 
                      label sum(Col2)'',toDate(Col1)''"; 0); "select Col2"; 0)))}})

0

  • First of all I appreciate the answer, but that's not what I'm looking for. Note that the value is cumulative. For instance, in 2018/October the correct value is $5629.29 ($2000 from 2018/April). – Scare May 14 at 22:17
  • are values in B column a valid dates? – user0 May 14 at 22:39
  • 1
    Yes! It could be another format, in general, it does not matter what format it is, but it's valid dates. – Scare May 14 at 22:42
  • @Scare answer updated – user0 May 14 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.