 SOLVED

# Conditional Sum

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!

4 Replies

# Re: Conditional Sum

@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

# Re: Conditional Sum

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!

best response confirmed by Grahmfs13 (Microsoft)
Solution

# Re: Conditional Sum

@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.

# Re: Conditional Sum

Awesome! Thank you so much