count empty cells until the next filled cell

Question

I have a table similar to the one below and like to automate the calculation of the sum column. The number of rows per day varies. I'm looking for a way to find the number of empty cells in the date column after the current row. This number can then be used to fill the sum column.

Is there regardless of the solution below a way to count the number of empty cells between the dates?

Date      |Value|Sum
----------+-----+---
16/07/2020|    2|  5
          |    3|
17/07/2020|    2| 10
          |    3|
          |    5|
18/07/2020|    2| 11
          |    3|
          |    5|
          |    1|

Answer 1

I don't think you need the answer to your first question to figure out the answer to the Sum.

With the entirety of column C blank, try this in C1:

=ARRAYFORMULA({"Sum";IF(A2:A="",,VLOOKUP(A2:A,QUERY({VLOOKUP(ROW(A2:A),FILTER({ROW(A2:A),A2:A},A2:A<>""),2),B2:B},"Select Col1,SUM(Col2) group by Col1"),2,0))})

If that doesn't work, it might be easier to demonstrate the idea on a sample sheet.

Answer 2

André, try this:

1.) Delete C:C entirely (including the header).

2.) Place the following formula into cell C1:

=ArrayFormula({"Sum";IF(A2:A="","",VLOOKUP(A2:A,QUERY({VLOOKUP(ROW(A2:A),FILTER({ROW(A2:A),A2:A},A2:A<>"",B2:B<>""),2,TRUE),B2:B},"Select Col1, SUM(Col2) Group By Col1"),2,FALSE))})

UPDATE:

Your post example shows headers. The formula I suggested, then, accounted for those headers. Since your actual sample sheet is different and does not use headers like the original post, you'd use this version:

=ArrayFormula(IF(A:A="","",VLOOKUP(A:A,QUERY({VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},A:A<>"",B:B<>""),2,TRUE),B:B},"Select Col1, SUM(Col2) Group By Col1"),2,FALSE)))

Answer 3

if you start from row 1 use:

=ARRAYFORMULA(IF(A:A="",,VLOOKUP(A:A, QUERY({VLOOKUP(ROW(A:A), 
 FILTER({ROW(A:A), A:A}, A:A<>""), 2), B:B},
 "select Col1,sum(Col2) group by Col1"), 2, 0)))

---

=ARRAYFORMULA(IFNA(VLOOKUP(A:A, QUERY(IF(B:B="",,VLOOKUP(ROW(A:A), 
 IF(A:A<>"", {ROW(A:A), A:A}), 2, 1)),
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''"), 2, 0)))