1

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|
Improve this question
0

3 Answers 3

Reset to default
2

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)))

enter image description here

=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)))

enter image description here

Improve this answer
3
  • Thank you very much. Is there regardless of this solution a way to count the number of empty cells between the dates?
    – André
    Commented Jul 18, 2020 at 16:42
  • Thanks a lot. It works, but why does the column "B" matter in this second formula?
    – André
    Commented Jul 18, 2020 at 18:56
  • it matters because otherwise you would get count for all the rows spreadsheet has. see F1 and notice F19 - docs.google.com/spreadsheets/d/…
    – player0
    Commented Jul 18, 2020 at 19:27
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.

Improve this answer
2
  • Matt, funny: you posted as I was posting, and our ideas were exactly the same.
    – Erik Tyler
    Commented Jul 18, 2020 at 16:06
  • @ErikTyler That'll happen :)
    – MattKing
    Commented Jul 18, 2020 at 19:55
1

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)))
Improve this answer
4
  • Unfortunately, it didn't work and results in an error while parsing the formula. I created a simple example sheet: docs.google.com/spreadsheets/d/…
    – André
    Commented Jul 18, 2020 at 16:22
  • The formula I provided (and the one Matt provided) assumed that your data column had a header, which is standard. It doesn't. I edited my post to show the version without a header, which works fine.
    – Erik Tyler
    Commented Jul 18, 2020 at 18:07
  • My real problem has a header and is much more complicated. I simplified the question and the example sheet.
    – André
    Commented Jul 18, 2020 at 18:53
  • 1
    The only problem for Matt and me was that the example in the post (with headers) didn't match the sheet (without headers). That is why the original formulas wouldn't have worked: because they were written for headers. But at the end of the day, you got it solved. That's the main goal.
    – Erik Tyler
    Commented Jul 18, 2020 at 23:55

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.