0

I have a spreadsheet with a data set containing hours:minutes:seconds. This dataset is a record of logged times per user (rows). The number of rows or columns isn't fixed. The number of columns is never fixed (constantly expanding for every new day logged). I want to create a new row based on the sum of each column. I assume I have to use ArrayFormula but I don't know where or how to approach.

Rows are correlated to a number of users.

Columns are generated to a number of days logged.

Here is an example dataset: https://docs.google.com/spreadsheets/d/1B05oOV8ZmcuHbm4v8iJLdZ6OAsO2ruEE4mOkv1PAPEk/edit?usp=sharing

The formula should auto update the row with the sums of the new columns because it should be flexible enough to allow for new users/dates.

enter image description here

  • I've noticed that F2: '02:01:48 is that only in your example sheet or all your imported data are in this format? – MARK MY ANSWER Jan 25 at 16:06
0

Dalton, a few things to start:

  1. Your "durations" in Column D and right are not actual durations (i.e., numbers) but text. This adds difficulty in adding them. Still, there is a workaround, which I include in my forthcoming formula.

  2. Math will be done with these "non-math" data points, and the result will be a pure number representing days and portions of a day (i.e., 1.5 would be 1.5 days or 36 hours). In order to see the results as durations, you'll want to select the entire row (by clicking the row number to the left of the row) for whichever row you place the forthcoming formula into and format it as durations using Format > Number > Duration.

Those things said, if you place the following formula into Column A (say a couple rows below your last ID), it should keep up with the flexible columns. Just keep in mind that you'll need to add or delete rows for each person that is added to or removed from the list, so that the formula moves up and down with your data (though as long as it is "somewhere" below your last ID in Column A, it should work).

The formula:

=ArrayFormula({{"","","Totals:"},TRANSPOSE(MMULT(TRANSPOSE(TIMEVALUE(IF(INDIRECT("D2:"&ADDRESS(ROW()-1,COUNTA(1:1),4))="","0:0:0",INDIRECT("D2:"&ADDRESS(ROW()-1,COUNTA(1:1),4))))),ROW(INDIRECT("A2:A"&ROW()-1))^0))})

0

the number of rows or columns isn't fixed

the number of columns is never fixed (constantly expanding for every new day logged)

In a long run, let's assume that you will have a higher expansion rate of columns (new days), than the expansion rate of rows (new users), therefore positioning this total row under your table would be "manageable" (otherwise you would need to place it on a new top row). semi-automatic solution:

paste this into A19 cell:

={"", "", "Totals:", ARRAYFORMULA(IF(LEN(D1:1), D2:2 + D3:3 + D4:4 + D5:5 + 
                                                D6:6 + D7:7 + D8:8 + D9:9 + 
                                                D10:10 + D11:11 + D12:12 + D13:13 + 
                                                D14:14 + D15:15 + D16:16 + D17:17, ))}

5

  • That is an excellent answer! Having this expanded to just be put on a seperate page as well wouldn't be absurd either! Thanks! – Dalton Smith Jan 26 at 2:56
  • Is there a way to avoid manually inputting the number of rows (perhaps a number I could set for X amount of rows)? – Dalton Smith Jan 26 at 3:36

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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