Conditional Sum

[taira727]

Hello,

I am trying to make timesheet for my team. Is there any good way to have calmative sum of each day with formula? I had some success with "sum if" function as shown in the first image. However - 1) I don't want to type date for each row, and day sum should be shown just once for each day. (In a nutshell, I don't want to type/show cell crossed out with red)

 

Ideally I want it to be like below image - G column showing daily sum only once.

Let me know if you have any ideas. Thank you!

[#2]

@taira727 this will work based on a couple of assumptions: a) the date on in column A is entered as a date, b) the user knows to only enter the date on the first line, and c) the user will enter a date after the last line.  Note that any/all of these could be accommodated for but would take additional tweaks in the formula:

=IF(A6,SUM(INDEX(F:F,MATCH(MAXIFS(A:A,A:A,"<"&A6),A:A))),"")

 see attached

[#3]

@mtarler 

Really appreciate your insight here. When I open the attached file, I see numbers under "day sum" column is not exactly sum of each day - any ideas how to make this work? 

Thanks again!

[#4 · Accepted solution]

@taira727   Ooops my bad I missed a ref.  Here is the corrected formula that gets copied down:

=IF(A4,SUM(INDEX(F:F,MATCH(MAXIFS(A:A,A:A,"<"&A4),A:A)):F3),"")

I also created a dynamic array version (i.e. just paste in the top cell and it auto fills down)

=LET(d,$A$3:INDEX(A:A,COUNTA(B:B)+1),t,$F$3:INDEX(F:F,COUNTA(B:B)),s,SEQUENCE(ROWS(t),1,1),**bleep**,SCAN(0,s,LAMBDA(c,i,IF(INDEX(d,i),INDEX(t,i),c+INDEX(t,i)))),IF(INDEX(d,s+1)>0,**bleep**,""))

see attachment.

[#5]

Awesome! Thank you so much