Search code examples
google-sheetsspreadsheetgoogle-docs

Sum vertically until empty cell on Google Sheets

This is the scenario. I need to get the sum of the values until it reaches a blank cell. After that it should start again calculating the sum after the blank cell. I need to add the fat and carbs of every ingredient of each dish individually. enter image description here

This is the expected result. That for each of hundreds of dishes. In this case I entered the data manually:

enter image description here

I tried with this but is adding all of the data from the column: 

IF(SUM(C3:C)="","",SUM(C3:C)))

I also tried with this formula but is not working: 

IF(C3:C="","",SUM(INDIRECT(ADDRESS(ROW(C3:C),COLUMN(C3:C400))&":"&"C"&MIN(ARRAYFORMULA(IF(C3:C400="",ROW(C3:C400),""))))))

This is the spreadsheet in case you need it.

Thanks in advance for all the help.

Solution

  • I would just make a small adjustment to your formula:

    IF(C2="",SUM(C3:INDEX(C3:C,MATCH(TRUE,(C3:C=""),0))),"")

    so the first total is 11 instead of 9.

    enter image description here

    This is an (experimental) array-formula version based on numbering each group using the presence of a Dish in the first row of each group as a marker:

    =ArrayFormula(if(A2:A="","",vlookup(countifs(A2:A,"<>",row(A2:A),"<="&row(A2:A)),
query({C2:C,countifs(A2:A,"<>",row(A2:A),"<="&row(A2:A))},"select Col2,sum(Col1) group by Col2"),2)))

    enter image description here