Forum Discussion
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!
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.
4 Replies
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
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.
