I need a very particular formula that would calculate the hours left in a month after some amount for them was logged each week. We start with 24h and subtract the time in each row. Seems very easy but there are three caveats:
- the count has to reset each month and the list is long
- it’s a dynamic data range so it has to work on an arrayformula or maybe byrow
- the newest dates are at the top so the subtraction has to run backwards
This might not sound very clear, but I have prepared an exact data sheet to show this problem. There are some columns with additional information about each row – the weeks also break to not spill over multiple months. Does anyone have any idea?
2
Here you have an option with SCAN. The SEQUENCE(ROWS(A2:A),1,ROWS(A2:A),-1) stored in the v variable allows to access each row backwards and doing the substractions and restart at 24. The SORT inverts the process so the results are displayed accordingly.
=SORT(SCAN(,SEQUENCE(ROWS(A2:A),1,ROWS(A2:A),-1),LAMBDA(a,v,IF(INDEX(A2:A,v)="","",IF(INDEX(B2:B,v)<>INDEX(B2:B,v+1),24-INDEX(E2:E,v),a-INDEX(E2:E,v))))),
SEQUENCE(ROWS(A2:A)),0)
If you want to put the formula in row 1 use VSTACK:
=VSTACK("Time left",SORT(SCAN(,SEQUENCE(ROWS(A2:A),1,ROWS(A2:A),-1),LAMBDA(a,v,IF(INDEX(A2:A,v)="","",IF(INDEX(B2:B,v)<>INDEX(B2:B,v+1),24-INDEX(E2:E,v),a-INDEX(E2:E,v))))),
SEQUENCE(ROWS(A2:A)),0))
2
Enter this formula in, say, cell I2 in your spreadsheet:
=24 - SUMIFS(E2:E$10000, A2:A$10000, A2, B2:B$10000, B2)
(If necessary, increase each instance of $10000 to some egregiously large value which would always exceed the number of rows in your table.)
Then drag (or copy/paste) the formula down the same column to the bottom of your table.
The mixture of relative row references (e.g. E2) and absolute row references (e.g. E$10000) will give you the results you require.
Further explanation: the SUMIF() criteria will ensure that each sum includes only the rows where both the year and month match the current row.
UPDATE
The OP has clarified in the comments that a single formula is required.
One way to do this is as follows. You will need to hold your nose.
Note: this works in Excel but not Google Sheets, as required by the OP.
=BYROW(A2:A10000,LAMBDA(row,24 - SUMIFS(INDIRECT("E" & ROW(row) & ":E$10000"),INDIRECT("A" & ROW(row) & ":A$10000"),INDIRECT("A" & ROW(row)),INDIRECT("B" & ROW(row) & ":B$10000"),INDIRECT("B" & ROW(row)))))
This uses the BYROW function to apply a formula similar to the first one posted above to each row in turn. The lambda function accepts each cell of the BYROW reference in turn, identifies its row number using the ROW() function, creates the appropriate range references (as text) and then converts them to actual references using INDIRECT().
For Google Sheets:
For Google Sheets you need to be a little more careful about two things: the ‘last row’ reference (which can’t exceed the size of the sheet) and the name of the lambda parameter.
This formula:
=BYROW(A2:A100,LAMBDA(cell,24 - SUMIFS(INDIRECT("E" & ROW(cell) & ":E$100"),INDIRECT("A" & ROW(cell) & ":A$100"),INDIRECT("A" & ROW(cell)),INDIRECT("B" & ROW(cell) & ":B$100"),INDIRECT("B" & ROW(cell)))))
works in your original sheet (as linked) as produces the correct results. If 100 rows isn’t enough, expand the sheet and change the formula’s references accordingly.
5