Google-sheets – Sum of rows until a condition changes

google sheets

I have a Google Sheet with the following format:

A           B       C       D
10-12-18    17.36   Start   
10-12-18    17.49   Stop    13
10-12-18    17.49   Start   
10-12-18    17.51   Stop    2
10-15-18    09.19   Start   
10-15-18    09.32   Stop    13

Column A is a date, column B a time, column C Start/Stop status and column D the duration of the two timestamps Start/Stop for each day.

Now I want to calculate the sum of all column D duration for each day.

This is how it should look like in the end (column E sums up all of the durations per day):

A           B       C       D       E
10-12-18    17.36   Start   
10-12-18    17.49   Stop    13
10-12-18    17.49   Start   
10-12-18    17.51   Stop    2       15
10-15-18    09.19   Start   
10-15-18    09.32   Stop    13      13

How can I achieve that? SUMIF? ARRAYFORMULA?

Best Answer

I have copied my column of formulas from Auswertung to Rohdaten!D:D. Then I used the following formula in Rohdaten!E2 and drag-copied it down to E256:

=IF(A2="";"";IF(A3="";SUM(D$1:D2)-SUM(E$1:E1);IF(IF(ISNUMBER(A2);A2;DATE(VALUE("20"&RIGHT(TO_TEXT(A2);2));LEFT(TO_TEXT(A2);2);MID(TO_TEXT(A2);FIND("-";TO_TEXT(SUBSTITUTE(A2;".";"-")))+1;2)))<IF(ISNUMBER(A3);A3;DATE(VALUE("20"&RIGHT(TO_TEXT(A3);2));LEFT(TO_TEXT(A3);2);MID(TO_TEXT(A3);FIND("-";TO_TEXT(SUBSTITUTE(A3;".";"-")))+1;2)));SUM(D$1:D2)-SUM(E$1:E1);"")))

An array formula would not work here, because you would be trying to use other conditional arrays inside of an array.

Usually, I explain how my formulas work. In this case, it's just too complex. Your raw data coming in is in multiple different formats including text, numbers and various formats of numbers; so a lot of the formula is just trying to normalize the data in Column A to be understood as relative dates.

Related Topic