5

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.

eera5607
  • 305
  • 2
  • 8

1 Answers1

13

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

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Do you have any approaches to achieving this with ArrayFormula? – Brody Robertson Apr 30 '21 at 19:51
  • I'm thinking if you numbered each group (maybe using countifs) you could do a 'group by' query to find the sum for each group? Will need to experiment with it. – Tom Sharpe May 01 '21 at 06:32
  • 1
    This is what I had in mind: =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))) – Tom Sharpe May 01 '21 at 06:50
  • Excellent, thanks. I will start with this and let you know how it goes! – Brody Robertson May 03 '21 at 17:08