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)

1 Answer 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

4
  • 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, 2019 at 22:17
  • are values in B column a valid dates?
    – user0
    May 14, 2019 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, 2019 at 22:42
  • @Scare answer updated
    – user0
    May 14, 2019 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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