CODEWITHSUNDEEP
excelexcel-formula
user14175128
user14175128

Reputation: 29

How to build accumulated sum per day in Excel?

I have an Excel worksheet where I write down all the hours per day I work on my project. During one day I just work some minutes here and there.

In one column of my sheet I would like to have the accumulated / added hours I worked at that specific day.

So once a row with day, start, and end is complete, the referring accumulated hours per day for this new row should appear. I would like to have this cell prefilled, so that I do not have to make any manual adjustments of the contents of this cell.

How has a formula in column accumulated hours per day have to look like to achieve this?

I need a formula for the rightmost column

Upvotes: 0

Views: 388

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34265

You could also try an array formula version:

=SUBTOTAL(9,OFFSET(C2,0,0,ROW(A2:A9)-1))-IFERROR(SUBTOTAL(9,OFFSET(C2,0,0,MATCH(A2:A9,A2:A9,0)-1)),0)-
(SUBTOTAL(9,OFFSET(B2,0,0,ROW(A2:A9)-1))-IFERROR(SUBTOTAL(9,OFFSET(B2,0,0,MATCH(A2:A9,A2:A9,0)-1)),0))

or more dynamically using Excel 365 Let and Sequence:

=LET(range,A2:INDEX(A:A,COUNTA(A:A)),r,ROWS(range),
SUBTOTAL(9,OFFSET(C2,0,0,SEQUENCE(r)))-IFERROR(SUBTOTAL(9,OFFSET(C2,0,0,MATCH(range,range,0)-1)),0)-
(SUBTOTAL(9,OFFSET(B2,0,0,SEQUENCE(r)))-IFERROR(SUBTOTAL(9,OFFSET(B2,0,0,MATCH(range,range,0)-1)),0)
))

enter image description here

Upvotes: 0

user7394563
user7394563

Reputation: 43

I would suggest creating a pivot table and there you can do many things.

Pivot table

Upvotes: 0

Enigmativity
Enigmativity

Reputation: 117124

You're looking for this:

=IF(A2<>A1,0,D1)+C2-B2

Ideally, I'd suggest avoiding references in the over row by using OFFSET. Try this instead:

=IF(A2<>OFFSET(A2,-1,0),0,OFFSET(D2,-1,0))+C2-B2

Upvotes: 2

Related Questions