Google-sheets – Create a row based on the sum of every column in a sheet

formulasgoogle sheetsgoogle-sheets-arrayformula

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

Best Answer

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

Related Topic