0

In a 'Records' sheet I have columns of timestamp, a duration, and a category (i.e. 13 minutes of 'B' was done at 18/01/2021 00:00).

Records data

In the 'Analysis' sheet I have a field where the Type can be defined and then using Filter the matching rows are shown and can be worked with to create a 'Sum since' tally.

Analysis sheet

I am failing to write a formula for a single cell that will produce the array in the D column in Analysis. I can do this just fine assuming there is no 'Type' by using Arrayformula and Sumif, but it seems that Sumifs cannot be used with array formula, and query cannot be used with duration data.

Can anyone give me any pointers or better yet a working formula? The correct answer would slot into a larger formula I already have for a moving weighted average.

EDIT link to test sheet: https://docs.google.com/spreadsheets/d/1-iPODUKQB4qRv2HNV4dZ-UwKkChzuKySu7YWEF0NJHQ/edit?usp=sharing

4
  • Please read how to and share a test sheet so as you can be easier helped. Also include the formulas you now use as well as the expected results – marikamitsos Feb 13 at 20:39
  • Thanks @marikamitsos I've updated the question with a test sheet link – user3623437 Feb 13 at 21:08
  • What would your expected results be for column E? Please update your sheet with them in column G. – marikamitsos Feb 13 at 21:31
  • The correct answers are in column D in the sample sheet but I am not able to produce them with a single arrayformula – user3623437 Feb 13 at 21:35
1

I added a new sheet ("Erik Help") with the following array formula in D2:

=ArrayFormula(IF(B2:B="",,SUMIF(IF(ROW(B2:B),ROW(B2:B)),">="&ROW(B2:B),C2:C)))

The initial IF(B2:B="",, just leaves cells blank if the corresponding cell from Column B of that row is also blank.

The opening IF(ROW(B2:B),ROW(B2:B)) of the SUMIF creates an array of all row numbers from B2:B, which can then be matched for each row against the condition to only include those that are greater than or equal to the current row (with durations in C2:C being summed accordingly).

3
  • Thanks Erik, that looks great! I do have a follow-up question if that's ok. Is there a way to achieve what you have done in D2 without relying on the B&C column data? I.e. is it possible to do it and only refer to the Type in A2 and the Records sheet? I have added a column to show how I have attempted this but it doesn't produce the same output. – user3623437 Feb 14 at 22:12
  • See additional sheet, "Erik Help 2." – Erik Tyler Feb 14 at 23:10
  • Thanks so much! – user3623437 Feb 15 at 14:12

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.