Search code examples
google-sheetssumgoogle-sheets-formulaarray-formulastextjoin

SUM contiguous values in column


Is it possible to SUM groups of contiguous values in a column without manually grouping them one by one the way I have in this picture?

enter image description here

sample sheet


Solution

  • =ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(CONCATENATE({"♥"&MMULT(
     SPLIT(TRANSPOSE(SPLIT(TEXTJOIN(" ", 1, IF(INDIRECT("A2:A"&MAX(IF(A2:A<>"", 
     ROW(A2:A), )))="", "♦", A2:A)), "♦")), " ")*1, ROW(INDIRECT("A1:A"&COLUMNS(
     SPLIT(TRANSPOSE(SPLIT(TEXTJOIN(" ", 1, IF(INDIRECT("A2:A"&MAX(IF(A2:A<>"", 
     ROW(A2:A), )))="", "♦", A2:A)), "♦")), " ")*1)))^0),IF(
     SPLIT(TRANSPOSE(SPLIT(TEXTJOIN(" ", 1, IF(INDIRECT("A2:A"&MAX(IF(A2:A<>"", 
     ROW(A2:A), )))="", "♦", A2:A)), "♦")), " ")<>"", "♥♠", )}), "♥")), "♠", ))
    

    0