0

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?

1

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.

  • Thanks a lot but I don't get it to work: docs.google.com/spreadsheets/d/… I have tried to accomplish that in the sheet tab "Auswertung" because the tab "Rohdaten" gets overwritten via a trigger once per day. – Peleke Nov 12 '18 at 10:23
  • I can no longer clearly understand or sort out what you're trying to do on that sheet. You have formulas stuck in everywhere, and I don't want to delete anything; but I also don't know why you have many formulas in there. Honestly, I would recommend that you look into hiring someone to help you. Everything about the sheet feels precarious to me, from the way data is coming in, to the way you're trying to set up sheets now. – Erik Tyler Nov 12 '18 at 21:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.